I have an Excel file with 4-digit codes in one of the columns. Some of the codes have a leading zero. In the file itself the column is formatted as Custom: 0000. For example the values are 825, 947, 1045, but due to formatting they are shown as 0825, 0947, 1045.
When I load the file into Power Query, the codes lose the leading zero and get changed to 825, 947, 1045. Subsequently, the values in my Pivot Table slicer are 825, 947, 1045, instead of desired 0825, 0947, 1045.
I changed the Data Type to Text in Power Query, but this did not help.
Is there a solution for my problem, without having to change the column in the original Excel file to text?
Thank you.
Blanka
I think I found a solution. I kept Data Type as Whole Number in Power Query Editor, but then I added a custom column with the following formula:
=if Text.Length(Text.From(
)) = 3 then "0"&Text.From(Code) else Text.From(Code)Maybe it's not the most elegant, but it's working for my needs right now.
I just love Power Query and I'm a big fan of Mynda's instructions too.
Hi Blanka,
Great to see you're having a go at writing formulas and finding solutions. An alternative is to use the function Text.PadStart to fix the lenght of your codes e.g.:
=Text.PadStart(
, 4, 0)Where 4 is the length you want the text, and 0 is the character you want to pad with if the
isn't already 4 characters long.Mynda
Thank you so much, Mynda.
This is much shorter and simpler than what I wrote. I'll give it a go at work on Monday.
Blanka
I was getting an error when I used =Text.PadStart(
,4,0).First it was "We cannot convert the value 825 to type Text." so I changed my Code data type to text.
The next error was "Expression.Error: We cannot convert the value 0 to type Text".
I added quotation marks to "0" in the formula and now it seems to be working fine:
=Text.PadStart(
,4,"0")
Well done figuring it out, Blanka.
Since Power Query doesn't apply formatting it makes sense that any number that starts with a zero cannot actually be a number, it must be text. Only in the Excel worksheet can we make numbers take on a different appearance with the use of custom number formats. i.e. in Excel we can make a number appear to have a leading zero and still retain its number data type, but in Power Query there are no number formats, only data types.
Mynda
