Total row on query

[Paul_Christie]

Ken

Given that the result of a query gets loaded as an Excel Table then it's possible to turn the Total row on. No problem there. How does one get the Total row to refresh with the query refresh?

Paul

 

[SteelReyn]

I just tried this, and wondering if you get the same thing. Create a table in Excel, use Power Query's From Table to add to Power Query, and close and load the table to an Excel sheet.

When I add a total row to the new table, it's default is SUBTOTAL(109,[ColumnName]), which equates to a SUM of the column. After adding another line to my source table and refreshing Power Query, the total row formula is changed automatically to SUBTOTAL(103,[ColumnName]), which is the same as COUNTA on the column. Even if I change it back to 109, it's automatically changed to 103 after the next data refresh. If I leave it as 103, it refreshes normally and that "total" is updated at the same time. So the total row does refresh, but won't keep the right formula.

Seems buggy, unless this is one of those times Excel is trying to be smarter than I am and not telling me why.

 

[Paul_Christie]

I've just done what you did and got the same result as you. Buggy is one word I'd use.

 

[Paul_Christie]

This is beginning to become a habit. I'm going to post an answer to my own question. The total row is a property of the Table not the Query.

In the Table Design tab, click Properties. In the dialog box that appears, check the box next to "Preserve column sort/filter/layout."

 

[SteelReyn]

Good find! That kept the total row formula the same on the refresh. I'm still going with buggy, though.

 

[Ken Puls]

This is actually an issue that's been there for quite a while. It's actually not a PQ issue, but rather something to do with the way tables were implemented (from my understanding... which to be fair is pretty limited.) Even worse than totals is using Calculated Columns on a table that is sourced from Power Query. (To keep it short... just don't.)

They are aware of it. Not sure how long till we see a fix though.