Forum Discussion
Solved
Summarize or aggregate multiple columns
Hi, My data set is like follows. Col 1 Col 2 Col 3 .... .... .... ABC ABC XYZ ..... .... .... ABC DEF ABC ..... .... .... DEF XYZ ABC ..... ..... .... ..... ...
Try this Custom Column, then split it into new columns
Please see attached file's Query Editor as well
=Text.Combine(
Table.AddColumn(
Table.Sort(
Table.Group(Record.ToTable(_),"Value",{{"result",each Table.RowCount(_), type number}}),
{{"Value", Order.Ascending}}),
"Merged",
each [Value] & "(" & Text.From([result]) & ")")[Merged],
", ")
Dear Zubair_Muhammad ,
Thanks for the code. The code works well.
I need some tweak in it. The code converts all the available column in a table. I need the columns to be specified.
Could you please kindly help with it.
Thanks
vissvess
Dear Zubair_Muhammad ,
Also, could you give me a suggestion as how to ignore counting if there are null values say out of n columns in a row, if there are 5 empties, the summarization gives me "(5), XXX(2),...." Something like that.
Thanks in advance.
In this case we can adjust the formula as follows.
You can specify the column names in place of the code in red font below
Text.Combine( Table.AddColumn( Table.Sort( Table.Group( Table.SelectRows( Record.ToTable( Record.SelectFields(_,{"Col 1","Col 2","Col 3","Col 4"}) ),each [Value] <> null and [Value] <> "") ,"Value",{{"result",each Table.RowCount(_), type number}}), {{"Value", Order.Ascending}}), "Merged", each [Value] & "(" & Text.From([result]) & ")")[Merged], ", ")
