Concatenate numbers together leaving a number

Sayth

Board Regular
Joined
Jun 4, 2010
Messages
212
I was creating a unique id for a column and wanted to concatenate the ascii numeric representations for the letters to the end of an existing id in powerquery.

Such as in this example
trainernumbertrainer_track Nameworking outanswer_id
81483RandwickCummingsJamesJames Cummings81483 + 17 + 0 +138148317013
81483MenahCummingsJamesJames Cummings
20602948RosehillCummingsJamesJames Cummings
20602948Agnes Banks/HawkesburyCummingsJamesJames Cummings
20602948Warwick FarmCummingsJamesJames Cummings
20602948Agnes BanksCummingsJamesJames Cummings

<tbody style="box-sizing: border-box;">
</tbody>

When I use '&' powerquery returns an error that it cannot use and with numbers. So this solution fails.
Code:
[trainernumber] & Character.ToNumber(Text.Start([trainertrack],1)) & Character.ToNumber(Text.Range([trainertrack],1,1)) & Character.ToNumber(Text.Range([trainertrack],2,1))
To get a working solution with slightly different but still unique answer I used '+'.
Code:
[trainernumber] + Character.ToNumber(Text.Start([trainertrack],1)) + Character.ToNumber(Text.Range([trainertrack],1,1)) + Character.ToNumber(Text.Range([trainertrack],2,1))

Just wondering though for reference how can I join them rather than having to add them?
 

Sayth

Board Regular
Joined
Jun 4, 2010
Messages
212
Have you tried converting the numbers to text first?
Thanks. Got it.

Code:
Number.FromText(Number.ToText([trainernumber]) & Number.ToText(Character.ToNumber(Text.Start([trainertrack],1))) & Number.ToText(Character.ToNumber(Text.Range([trainertrack],1,1))) & Number.ToText(Character.ToNumber(Text.Range([trainertrack],2,1))))
 

Forum statistics

Threads
1,085,433
Messages
5,383,646
Members
401,843
Latest member
CallumMcEwen

Some videos you may like

This Week's Hot Topics

Top