How to keep leading zeros when I import data from an excel file into Power BI?

[gusdahu]

Power BI Community,

I am importing data from an excel file into Power BI. The data contains ID numbers with leading zeros. When I import the data, the leading zeros disappear. I converted the column into text hoping this would solve the problem, but it didn't. How do I include leading zeros in my data set?

Regards,

Gus Dahu 

[IanT]

Just thought I'd add to this as I had the same problem but for some reason there was no 'Changed Type' step to delete, I fixed the issue by using 'Replace Values' on the problem column as below. Please note though that my desired value length was always 6 characters, so amnend as necessary.

I first added a dummy replace value step replacing null with null and then amended the text in bold below.
Original 

#"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,null,Replacer.ReplaceValue,{"Cost Centre"})

Amended
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1",each[Problem Column],each Text.PadStart([Problem Column],6,"0"),Replacer.ReplaceValue,{"Problem Column"})

Hope it helps someone.

[v-yuta-msft]

Hi gusdahu,

Delete the step "Change Type" and check if "Zero" still disappear.

Regards,

Jimmy Tao

[drewlewis15]

Two things to check for:

1. Make sure the Excel file you are using actually has the leading zero in the value of the cell... There are custom formatting options within Excel that disply the number with the leading zero without actually adding the zero the value of the cell.  Just make sure the data in your Excel sheet is being formatted as text.
2. The Query editor usually automatically applies a formatting step after the "Source" step.  If you are changing the column to TEXT after that automatic step, then your leading zeros will not appear, as they were taken out in that automatic step. You would need to delete that automatic step and then change the column to TEXT at that point (immediately after the Source step).

Let me know if one of these two solutions resolve your issue.

[gusdahu]

Hi drewlewis15,

Is the step after the "Source" step you are referring to know as "Navigation"?

Regards,

Gus Dahu

[drewlewis15 · Accepted solution]

Look for a "Changed Type" step.  That would be the step to delete.