Forum Discussion

vissvess's avatar
vissvess
Icon for Helper V rankHelper V
7 years ago
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   ..... ..... .... .....  ...
Need Help
Tips and Tricks
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
7 years ago

vissvess 

 

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],
", ")