Power Query group by

[SbusyCR]

Hi,

In Sheet1 the table above is the Input and the below the desired output.

Sheet2 displays what I could achieve with the Group By advanced option. My source file has a lot of columns (more than 50) and I would like to know if there is a better option than having to use Add grouping for each column.

Cheers!

 

[Nebu]

Hi:

In the power query editor, select all the columns you want to group and click on the "Group-by" option from the menu. This will group all the selected columns at once.

Thanks

 

[Chihiro]

Personally... I'd recommend separating out dimension tables from fact table.
In real data, I'd wager you have some columns that are related and should be on same dimension table (such as customer data), which can be separated out from fact table.

PowerQuery (Get & Transform) and PowerPivot (Data Model) are both optimized for vertical compression. Having too many columns will cause large overhead in data processing.

Consider normalizing data for analysis and build star schema for your data (with relationships, dimension side being one and fact side being many).

 

[SbusyCR]

Thank you both!