Excel.Workbook missing data

[trungduc_acc]

Hi!

When I query data from *.xls file use Excel.Workbook() function, data in some cell is missing. Detail as below:

Data Source: (In DataNoFormat.xls file - Export form a local program)

Result query: (In Book1.xlsx file - Missing text data in cell A13 and A15)

+ I change delayTypes parameter in Excel.Workbook function to true/false, but query result not change.

+ If query from DataFormatGeneral.xls (Copy from DataNoFormat.xls then open and Format Cells to General), result as query from DataNoFormat.xls file.

+ If query from DataFormatText.xls (Copy from DataNoFormat.xls then open and Format Cells to Text), result is correct, no missing data.

+ Use Power Query in Excel or in PowerBI receive same result.

Mentioned files link here:

https://1drv.ms/u/s!AjR7DINn-uTQgpgE7rGdyjOcdZFzzg?e=VSxWpP

Why data is missing in two the first case? I want receive correct result with DataNoFormat.xls as with DataFormatText.xls file. Please give me the solution. Thank you verry much!

[808fr]

I struggled with this problem until I figured out that I could import the entire worksheet untyped by changing the data load settings on the file.   Now I don't have to edit the excel before import.  PQ/M like this: 

let
Source = Excel.Workbook(File.Contents("<path to file>.xlsx"), false, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data]
in
Sheet1_Sheet

 

[trungduc_acc]

Sorry. I was wrong. This problem was unsolved with update

I just tried by your solution with the file in this link: Link 
The problem is still and not resolved
Can you try with this file and let me know the results?

Thanks.

[trungduc_acc]

Thanks @808fr 

I remember also did so but it's not working, before.

Now, seem this problem was fixed with PowerBI (and excel) update.

I don't have this problem again

[mussaenda]

Hi @trungduc_acc, 

@Jimmy801 has good points.

If you are open with power automate,

You can automatically convert all the .xls files from one folder to .csv files and save to another folder.

Then use the .csv files as source.

You can also find links from google. 

Also, you are not able to do a refresh in Power BI service using .xls files anymore.

This is just my insight.

[trungduc_acc]

Thank you @mussaenda , @Jimmy801 .

I wan't store original source data files. So, save as original files to *.cvs, *.xlsx or transform data from range to table is final solution. I prefer format some column to text to solve this problem. It is the least change my source files. Recently, I use Power Automate Desktop to do steps below:

1. export *.xls data files, save to folder - this folder sync a sharepoint documents (dozens of files per time)

2. Open excel and then run a macro to format some column in *.xls files to Text.

3. Query data from folder store this *.xls files

This workflow is ok but If this problem is solved, I don't make step 2.

Step 2 can replace by use Power Automate to creat .csv file from .xls when .xls file add to sharepoint , then query this .csv file ...vv. This is a good idea, but I want to know missing data when query from .xls file (use Excel.Workbook function) is a problem of power query and it can not solve by power query and step 2 is required in one way or another. Seem, it's true.

Thanks again for your response @Jimmy801 , @mussaenda 

[Jimmy801]

Hello @trungduc_acc 

I made the experience that files created by 3rd party are kinda corrupted.... meaning they don't use Excel to save the file, but create this directly with the third party. This often leads to incomplete datasets. I cannot read xls-files and so I just saved the file with Excel as xlsx and there are no problems. So the chances are big that this is exactly your problem.

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

[trungduc_acc]

Thanks Jimmy.

Recently, I has two solution to solve this problem:

1. Save as *.xls to *.csv (Faster query, but more step before query)

2. Format data source to Text (open source file by Excel and Format Cells then save)

I tried your solution, save as source file to *.xlsx and then query. Then I got a correct result. But I want not do more this step. I want only use query to solve problem.

About Source file is not true *.xls type file. I tried to creat new file by Excel 365. I open new blank excel workbook and input exactly value or text in cells A2, A4, A13, A14, A15 same as value or text in DataNoFormat.xls file then save this workbook to *.xls file format. When I query this new file, result query incorrect, text in cell A13, A15 is replaced by null => Problem happen with true *.xls file format.

Why? I don't understand. Locking forward to get other solution from you which do not more step before query.

Sorry for my english not good.

Thank you.

[Jimmy801]

Hello @trungduc_acc 

There are some problems with querying .xls-files as well as with .xlsx-files and this really a pity. But this is a fact. What you can do with .xls-files is to maintain your data into a real table, then it should work. But if you don't have the possibility to change your source-file then your are in troubles. 

hope this helps

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