Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[EMMY ALAMI]

Dear Power Bi Community,

I have an issue with the group by function where the combined values are combination of 2 fields :

Field 1 = numbers

Field 2 = text

 

I’ve used the following formula to group rows :

= Table.Group(#”customized column”, {“FactorA”, “FactorB”}, {{“Combined value”, each Text.Combine((List.Sort( [Field1 & Field2], Order.Ascending),”#(lf)”), type nullable text}})

 

with this formula, rows into each combined value is sorted but as following :

1 xxxx

10 xxxx

2 xxxx

while I’m looking for an order as :

1 xxxx

2 xxxx

10 xxxx

Any ideas to help with are more than welcome !

Many thanks in advance for your help !  

Regards,

——————————
EMMY ALAMI
Data analyst
——————————

[Sharavan Kumar]

Check the data format of that number column 

——————————————-

[EMMY ALAMI]

HI,
It’s a number bit once mergerd with the etxt format it’s a text
Thanks for your reply !

——————————
EMMY ALAMI
Data analyst
——————————
——————————————-

[Fredy Chen]

Can you sort before the combine?

——————————
Fredy Chen
——————————
——————————————-

[Amon Seagull]

If you like this approach (which I do, if it fits your problem), take care that sorting is not guaranteed to work if you don’t apply a buffer step. Imke Feldman raised that here:

https://community.powerbi.com/t5/Community-Blog/Bug-warning-for-Table-Sort-and-removing-duplicates-in-Power/ba-p/810390

She also has a link to her dialogue with MS on it.

——————————
Amon Seagull
McKinney
——————————
——————————————-

[Sharavan Kumar]

The try to convert fixed text 001, 002…

——————————————-

[Nancy Peterson]

If you still have the number column in your table, you can use it to sort. Go to the data view and select the text column you want to sort. Select the Column Tools menu and click on the button for “Sort by Column”. Select your number column and now this new text field will be sorted by the number column.

——————————
Nancy Peterson
Senior Business Analyst
Pitsco Education
Pittsburg KS
——————————
——————————————-

[EMMY ALAMI]

Many thanks ! 
I finally used the method of adding leading zeros for Nbres that are <9 so at the end I have a list : 01 / 02/ 03/ …. / 10 / 11/ …
and it works 🙂 
Thanks again !

——————————
EMMY ALAMI
Data analyst
——————————
——————————————-