Summarize or aggregate multiple columns

[vissvess]

Hi,

My data set is like follows.

Col 1   Col 2   Col 3 .... .... ....

ABC    ABC    XYZ   ..... .... ....

ABC     DEF   ABC   ..... .... ....

DEF     XYZ    ABC   ..... ..... ....

.....         .....          ......      ..... ..... .....

I need this to be summarised as follows.

Agg1         Agg 2      Agg3      ...

ABC(2)     XYZ(1)                     ...

ABC(2)     DEF(1)                     ...

ABC(1)     DEF(1)   XYZ(1)  ...

.....         .....          ......      ..... ..... .....

I was trying to achieve this in power query.

Any kind help would be appreciated.

If not possible with power query, DAX support for new column in modelling of power bi would also be appreciated.

Thanks in advance.

[Zubair_Muhammad]

@vissvess 

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

[vissvess]

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]

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]

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

[Zubair_Muhammad · Accepted solution]

@vissvess 

I created a small sample file as well with above formula

Please find it attached