Forum Discussion
xls missing first column
Hi. I have recive new bach of data from our web developers in xls format and meet strange begavior in power query grabber.
I try to load single file and folder, but in both case i dosn't see first column of data in power query it become 5th and have null in data.
Here is a sample of a file.
https://drive.google.com/file/d/1SobVktgXwPwagMp1uSTDQKohJuZW205o/view?usp=sharing
In xls it is look lile:
In power query i found this resoult:
If i change order of first two columls i lost data from first column anyway, and can't understad what reasont of that behavior.
5 Replies
Can you share your Power Query code from Advanced Editor?
Also, not able to access the link provided.
Please share the sample file again
Hi, here it is.
let
Source = Excel.Workbook(File.Contents("F:\PowerBi\Web\OrderInfo\2009-08.xls"), null, true),
Orders1 = Source{[Name="Orders"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Orders1,{{"Column1", Int64.Type}, {"Column2", type datetime}, {"Column3", type text}, {"Column4", Int64.Type}, {"Column5", type text}})
in
#"Changed Type"Please share your sample file and excle workbook which is being used as a source
Hello Zaknafeein
there are some weird problems when reading a xls-file. Maybe this file was also created by a 3rd party software.
So here some solutions to it
- try format your data as real table and then read the data from the table and not from the sheet
- try to open the file and save it as the same file once more
- save it as XLSX-file
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
