Group and sum multiple columns using power query-CodePudding

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.

CodePudding user response：

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"