Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
I created a small sample file as well with above formula
Please find it attached
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], ", ")
I created a small sample file as well with above formula
Please find it attached
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.