Forum Discussion

vissvess's avatar
vissvess
Helper 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
vissvess's avatar
vissvess
Helper V
7 years ago

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

vissvess's avatar
vissvess
Helper V
7 years ago

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.

 

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community 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],
", ")