Log In
Register
Home
Active Member
June 11, 2020
Offline
Hi all
I am very new to Power Query and am struggling with the M language it uses. I see the potential it has so have embarked on a project that will need me to utilise it if I am to succeed.
Very long story short...I have a bunch of alphanumeric values in one column - some start with a letter, some a number. If the value starts with a number, then I need the new column to add a zero to the beginning of it (and copy across), otherwise just copy the existing value across as is.
I would really appreciate it if someone could point me in the right direction...example in Excel attached >>>
Regards
Amar
October 5, 2010
Offline
Hi Amar,
Yes Power Query is a fantastic tool but for this problem you don't need to use it. You can achieve what you want with functions :
=IFERROR(IF(NUMBERVALUE(LEFT(B2,1)),"0"&B2,B2),B2)
Please see attached file.
regards
Phil
Active Member
June 11, 2020
Offline
Hi Phil
Thank you for coming back to me so quickly, much appreciated.
The reason I need that task to be done in Power Query is so that it is automated. The raw dataset I am using is vast and in bad shape, so there are steps previous to that, that PQ is already preforming (perfectly) and there will be steps afterwards.
The first two digits of that field represent a Department and I have a mapping table that I will pull in afterwards on the basis of those two digits - along several other things before I can complete the model.
I am sure that formula will come in handy at some point though, thanks!
Regards
Amar
July 16, 2010
Offline
Hi Amar,
This is why it's very important to give the whole picture of what you're trying to do and why.
You can use this Power Query formula in a 'Custom Column':
= if Value.Is(Number.FromText(Text.Start([Column1],1)), type number) then "0"&[Column1] else [Column1] otherwise [Column1])
Mynda
1 Guest(s)
