Forum Discussion
Excel import not consistently recognising blank first column
Thanks - I tried setting InferSheetDimensions to false but you're right it still uses excel's used range. I don't have control over the file creation unfortunately but I can interfere if a file is recieved and breaks the flow. A bit faffy - hopefully a feature to control the import is added soon. Thanks!
Microsoft EmployeeThe two files likely have different "used range" dimensions. There are instructions for how to inspect the dimensions here:
https://learn.microsoft.com/en-us/power-query/connectors/excel#how-to-diagnose-incorrect-dimensions
If you control the creation of the files, you may be able to remedy this by clearing any formatting/values/etc. from the first column. Even though the cells are blank, there may be some formatting or other metadata that's causing them to be included in the dimensions.
Alternatively, you could edit the first column in all the files to include something (even just a space) so that they're all consistent in having the real data start in Column2.
You could also try using the InferSheetDimensions option of Excel.Workbook. I don't recall if it will ignore leading null cells, but it would be worth a try.
Thanks - I tried setting InferSheetDimensions to false but you're right it still uses excel's used range. I don't have control over the file creation unfortunately but I can interfere if a file is recieved and breaks the flow. A bit faffy - hopefully a feature to control the import is added soon. Thanks!
Hi poinfrettia ,
Just checking in — has your issue been resolved? 😊
If so, it would be great if you could share the steps or the final solution you followed, so others facing a similar issue can benefit from it as well.
Also, please consider marking it as the accepted solution if it helped, this really helps the community!
Regards,
B Manikanteswara Reddy
- Ehren
Can you try setting InferSheetDimensions to true instead of false? The default is false, so setting it to false won't change anything.
Ah it was already true, I thought that was the default? It seems like neither way changes it regardless unfortunately.
- Ehren
Ok. Then your best bet would be to write logic in M that detects whether the data starts in Column1 vs. Column2, and handles both cases.