• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power Query group by

S

SbusyCR

New Member
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!
 

Attachments

  • Forum.xlsx
    19 KB · Views: 6
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
 
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).
 
You must log in or register to reply here.
Back
Top