Import zeros from Excel

[lekkerbek]

I have an Excel file which shows 0 before the actual number. For example

000280

* When I change the data type (in Excel) to "text" the 000 disappear.

* When I leave it as is and try to import it, it also eliminates the 000. When I remove the automatic "change type" in the query editor it remains the figure without the 000. 

So the normal solution would be to change the column type to text, but that doesn't work here either.

Anybody have a solution?

[v-haibl-msft]

@lekkerbek

If I change the data type (in Excel) to “Text”, the 000 will persist.

Then import it to Power BI, go to the “Navigation” step, the column data type will be Any and the 000 will also persist.

I’m using Excel 2016 and latest PBI Version: 2.38.4491.282 64-bit (August 2016)

Best Regards,

Herbert

[lekkerbek]

Thanks for all your suggestions. Normally the "convert to text" works, but somehow with this dataset it doesn't.

The copy/paste to a new table actually works, but is time consuming as it doesn't accept all lines in one copy/paste "session".

I'm able to export to XML and when I import that it works, so I'm guessing that's the way to go.

[v-haibl-msft]

@lekkerbek

If you can get your problem solved by exporting to XML then importing, you can mark your last post as solution to close this thread.

Best Regards,

Herbert

[lekkerbek]

I would consider this as a workaround and not so much as a solution.

I mark the copy/paste suggestion as the accepted solution.

[Framet]

Hi There,

In theory you need to edit the query and tell it the data type that you want as when you first load the excel file Power BI will be attempting to establish the best data type for each column and subsequently removing your 0's and converting to an integer.

The odd thing is that if you remove the change type step the data should still have leading 0's and in the small test below this was the case. I know you mentioned doing both seperately but have you tried both setting the column to text in excel and removing the change type or amending it as below? I imagine you do need it as type text to avoid excel stripping the leading zero's on saving the file.

For example:

This simple excel table loaded into Power BI:

ID is general format, REF is text.

ID	REF
1	000125
2	000144
3	000887
4	000889
5	000482

If you edit the query last step applied to the data "Changed Type":
= Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"REF", Int64.Type}})

You can simply change this manually to:

= Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"REF", type text}})

Or right click the column you don't like the data type on and choose "Change Type" -> Text.

Power BI will then retain your leading zero's. Or at least did for me.

Hope this is of some help.

Thomas

[Seth_C_Bauer · Accepted solution]

@lekkerbek If you set the format of the column/worksheet to text first, then import/copy-paste the values the "00's" should stay... Give that a shot, but in my experience that works.