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   ..... ..... ....

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

 

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.

Need Help
Tips and Tricks

5 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    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'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.