Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I need help to sort some columns in my report. The columns contain a list of strings.
For example, my original table looks like the following:
ID | Type1 | Type2 | Type3 | Name |
ID1 | English, Italian, Yen, French | Spanish | Greek, Armenian | ID1-name |
ID2 | French, English | Yen, Korean |
| ID2-name |
ID3 |
| Italian, Arabic |
| ID3-name |
After sorting the columns Type1, Type2 and Type3 in alphabetical order, I would like my table to look like this:
ID | Type1 | Type2 | Type3 | Name |
ID1 | English, French, Italian, Yen | Spanish | Armenian, Greek | ID1-name |
ID2 | English, French | Korean, Yen |
| ID2-name |
ID3 |
| Arabic, Italian |
| ID3-name |
Can somebody pls help?
Thank you
Not sure what analysis you have planned, but you might consider splitting the languages and unpivot. In any case, you can add a custom column to get your sorted values. It is easier to do this in the query editor, with a formula like this in the new column. It will create a list from your text, sort it, then combine the values back again.
= Text.Combine(List.Sort(Text.Split([Column1], ", ")), ", ")
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks @mahoneypat
Actually, those columns are dynamically created in Power Query and in my real data, there are not languages. Also, those lists are much longer. The goal was to create and sort them dynamically in M, if possible. But that was really complicated. I have a related post where this was discussed in the Power Query section.
Otherwise, is it possible to just sort them in the report in DAX?
Thanks.
This really is better done in M/query. Did the suggested M code not meet your needs? If the columns are generated in the query editor, can you add List.Sort into the mix to have them sorted when first created (to save processing time)?
Your other option is to split the values and unpivot (also in the query editor). Once you do that, you can use a CONCATENATEX() expression on the DAX side to put them back in sorted order.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you again at @mahoneypat
Actually, this problem was already solved in M as described here .
I wanted to know if there was a better and cleaner solution in DAX.
Thanks again.
Thanks for the explanation. This could be done in DAX with the approach found in this post
In the same expression you could re-concatenate things with CONCATENATEX using the order by expression or ASC or DESC.
But I still recommend M for this type of transformation.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |