I’m newbee to PQ, but I have a task to do.
I would appreciate the help if it will be provided)
I have the following table:
| YEAR | MONTH | ID | Option | Q_1 | Q_2 | … | Q_100 |
|---|---|---|---|---|---|---|---|
| 2020 | JANUARY | 1111 | plan | 23452 | 23105 | … | 549 |
| 2020 | JANUARY | 1111 | plan | 23453 | 23106 | … | 530 |
| 2020 | JANUARY | 1111 | plan | 23454 | 23107 | … | 510 |
| 2020 | JANUARY | 1111 | fact | 34689 | 33000 | … | 467 |
| 2020 | JANUARY | 1111 | fact | 34690 | 33010 | … | 477 |
| 2020 | JANUARY | 1111 | fact | 34691 | 33020 | … | 488 |
| 2020 | JANUARY | 1111 | fact | 34692 | 33045 | … | 490 |
I need to group table by first 4 columns and sum the value from columns starting with "Q"
So, the output should be:
| YEAR | MONTH | ID | Option | Profile | SUM of every match |
|---|---|---|---|---|---|
| 2020 | JANUARY | 1111 | plan | Q_1 | 70359 |
| 2020 | JANUARY | 1111 | plan | Q_2 | 69138 |
| 2020 | JANUARY | 1111 | plan | … | … |
| 2020 | JANUARY | 1111 | plan | Q_100 | 1589 |
| 2020 | JANUARY | 1111 | fact | Q_1 | 138762 |
| 2020 | JANUARY | 1111 | fact | Q_2 | 132075 |
| 2020 | JANUARY | 1111 | fact | … | … |
| 2020 | JANUARY | 1111 | fact | Q_100 | 1922 |
For sure, Year, Month and ID have more option than 1; Option has more options than 2.
>Solution :
Unpivot, then group, as follows
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"YEAR", "MONTH", "ID", "Option"}, "Profile", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"YEAR", "MONTH", "ID", "Option", "Profile"}, {{"SUM of every match", each List.Sum([Value]), type number}})
in #"Grouped Rows"
