Log In
Register
Home
August 21, 2019
Offline
Hi,
I got a similar case wanted to sum up Month for each column and display it, understand that UI is not able to achieve it and need M Code, had tried the recommendation in the following blog but it doesn't work for me.
is there any simplify way ?
Thank you
July 16, 2010
Offline
Hi Chris,
You can turn on the Total row in the Excel Table (as opposed to adding it in Power Query)? Table tab > check the box for the 'Total Row'. You'll need to insert the totals for the other columns as it will only automatically add it for the last column.
Mynda
August 21, 2019
Offline
Hi Mynda, so you are suggesting using excel table properties instead of M Code in PQ, However when I click refresh all the total formulae are wiped off
attached the source csv file and the PowerQuery before refreshing
Thank you !
July 16, 2010
Offline
Hi Chris,
I can't reproduce the problem. It doesn't wipe the total row out for me. If I filter the GrpBy_Card_Company query to exclude, say Visa, then Close & Load, the table reduces by one line and the total row moves up accordingly. If I then edit the query again to put Visa back in, the table increases by one row and the total row moves down by one.
Refresh All also doesn't cause any issues.
If you can't get it to work I would suggest creating a PivotTable for the totals. Power Query isn't the place for summarising data. You should be using Power Query to clean your raw data ready for summarising with tools like PivotTables and formulas.
Mynda
August 21, 2019
Offline
Hi Mynda,
Found out the reasons, need to "check" the preserve column sort/filter/layout under table > External data properties, by default it is uncheck, probably you are using office 365 don't have such problem
cheers and thanks !
1 Guest(s)
