Pivot a Column then use Group By to consolidate, with changing Column Names

[kingcondie]

Hello Knowledgeable Forum Members!

I have a query that pivots a column and then uses group by to sum the data in the columns that were created by the pivot. When I refresh the query, the pivot will create a different number of columns with different names. Is there a way to modify the M code to summarize based on the new column names?

Thanks for any help!

 

[Rudi]

I'd use two queries. One to pivot the columns (as you have) and a second to calculate the record totals. Then you can merge the two tables together to get the totals per row. By doing it this way, the result is dynamic and will change as the field names change.

See the sample workbook for a working demo.

View attachment Example.xlsx

 

[kingcondie]

Rudi, that is BRILLIANT! Thank you for the great suggestion!

Just in case anyone else is reading this, someone else suggested deleting unused fields and then doing the pivot so that group by is not needed. That is not a fix for everything but more simple if it will work. I really appreciate the dynamic result you shared.