Power Query: Force Excel File Import to Use Text Type for All Columns

[WALEED]

Gents,

My issue is when initially importing; some references like "00" get read as a number and automagically changed to "0". Converting back to text in Power Query won't revert the damage done!

I'm using:

Source = Excel.Workbook(File.Contents("B:\BP\Work Exchange\Transformation Power BI\DATA.TR.xlsx"), true, null),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

I've tried changing the last part of the first line "null" into true/false without success.

I've tried leaving the headers in the first row until a later stage but they still get read as (ABC/123) rather than (ABC) which meddles with my values all the same.

I've tried turning the setting off for auto-detection of types. It still changes the values automatically!

The excel sheet imported had all its columns changed to "text" type in Excel before importing.

[MarcelBeug · Accepted solution]

When I try and do the same, this is the result after the second step of the query (similar to what you posted), including leading zeroes:

 

First at the Changed Type step, the column types are changed to numbers, which can easily be adjusted:

So if your leading zeroes are already gone at the Navigation step, it looks like something may be wrong in Excel.