Group and sum multiple columns using power query

Question

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.

thanks for posting data as data instead of as an image!

horseyride
– horseyride

2023-02-01 12:31:41 +00:00
Commented Feb 1, 2023 at 12:31 — horseyride
– horseyride, Commented Feb 1, 2023 at 12:31

horseyride · Accepted Answer · 2023-02-01 12:29:17Z

1

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"

edited Feb 1, 2023 at 12:29

answered Feb 1, 2023 at 12:23

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Group and sum multiple columns using power query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related