How avoid reading data as numeric with Query towards XLSX Workbook?

[joel_h]

I have an Excel xlsx workbook generated by a export from a IT system. I'm trying to leveraging Excels awesome Query language feature do some ETL operations before the data will be sent on to another system.

TL;DR
I'm stumbling on an issue that my source data is interpreted as numeric data when loading into Query, e.g. values like 900-3893 are converted into 201111.

The conversion seems to already happened at the time I can edit the Query in Excel's Query Editor, so I have no way of getting that original data back, such as by changing back the type to text.

Any ideas how to resolve this issue? The restrictions here is I don't want to alter my original Excel Worksheet. The data type for the columns in question (e.g. Employee ID) is set as General in Excel. Also, I don't want to use other tools than Excel (I'm running 2016) if I can help it. I'm not keen on using something like Power BI desktop, since I want other (Excel only) users to be able to edit this solution when I have left this project.

More thoughts
I don't know why that specific conversation (900-3893 into 201111) happened, but I don't feel it very relevant here, I want to prevent the conversation taking place.

I'm thinking that it should be possible to somehow instruct Excel and Query to what data type to read from columns or read avoid the initial conversation some other way without touching the source workbook.

Here are some screens to give you guys some more context how I import the data source today:



Connection string?

If I open up the Connection Properties towards the Workbook, it looks as follows. Might it be a way for me to adjust how Excel reads the source data here, by doing something with the Connection String, or by using any of the other features here maybe?




Any ideas or solution would be much appreciated!

I really want to be able to import the data source without modifying it, or changing its format to something other than xlsx.

 

[MarcelBeug]

Can you share a file with dummy data where you experience this issue?

I'm not able to reproduce the issue and I also wonder why you have column headers in the preview pane (I have Column1, Column2 ..., and the headers from the worksheet are on the first data row).

Otherwise: if you choose button "Edit" then you enter the query editor in which you may be able to adjust the data type.
The generated code probably looks like this:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\MrExcel Power BI\How avoid reading data as numeric.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}})
in
    #"Changed Type"

 

[SpillerBD]

TL;DR
I'm stumbling on an issue that my source data is interpreted as numeric data when loading into Query, e.g. values like 900-3893 are converted into 201111.

The conversion seems to already happened at the time I can edit the Query in Excel's Query Editor, so I have no way of getting that original data back, such as by changing back the type to text.

It may be an issue with the export. Some systems export to Excel Format, but fail to ensure the preservation of the original data. The data from that system should be done as TEXT values. I run into this same problem with Oracle exports to "Excel" format but the files open with an error and then the certain text values have been interpreted as numeric and fouled up.
For this reason I actually prefer Oracle to give me a Text file since PQ can handle the conversion. (Just gets tricky when a field word-wraps )

You can create the Query in either PowerBI-Desktop or Get and Transform(PQ) in Excel. You can then copy and paste the queries from one to the other.