topic Re: Excel import not consistently recognising blank first column in Dataflow

Excel import not consistently recognising blank first column

poinfrettia — Wed, 14 May 2025 09:21:54 GMT

Hi there, I have a Gen 2 Flow which imports lots of identical excel workbooks in a folder. Column A in all of them is blank - but I'm getting errors as sometimes the Gen 2 Flow imports this first blank column, and sometimes skips it.

I understand this is based on excels 'used range' default. Can I force the import to start in column A somehow? Or define the specific range?

Re: Excel import not consistently recognising blank first column

miguel — Wed, 14 May 2025 18:19:12 GMT

Could you please share the M code that you're using? You can get it by going into the "Advanced editor" of your query in the "View" tab. If you're using a function to drive the logic, please share that custom function as well

Re: Excel import not consistently recognising blank first column

Anonymous — Thu, 15 May 2025 10:13:48 GMT

Hi poinfrettia ,

Thank you for reaching out to Microsoft Fabric Community Forum.

miguel Thank you for your quick response.

poinfrettia Yes, you're right. Could you please try the below M code after

We can explicitly define the range of data in Advanced Editor in View tab.

After the Navigation step in M code, you can define the columns as below:

#"Selected Columns" = Table.SelectColumns(#"Navigation 1", {"Column1", "Column2"}), // Replace with actual column names

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos " – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy

Re: Excel import not consistently recognising blank first column

poinfrettia — Thu, 15 May 2025 13:35:39 GMT

Hi Miguel and thanks for replying - it's actually the very first step, i.e. let Source = Excel.Workbook(Parameter, null, true), Navigation = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data].

When the Gen 2 Flow imports the spreadsheet, it automatically determines which column (A or B in this case) should be "Column1" based on what Excel indicates is the 'used range' on the sheet. If column A is blank, usually it decides column B should be Column1. The problem I'm having is that for some sheets, even though column A is blank in all of them, it's setting column A as Column1 in some files and column B in others.

Re: Excel import not consistently recognising blank first column

poinfrettia — Thu, 15 May 2025 13:37:10 GMT

Hi and thanks for replying - I've added more details to Miguel's reply which will hopefully clarify the problem better.

Re: Excel import not consistently recognising blank first column

Ehren — Thu, 15 May 2025 16:02:17 GMT

The 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.

Re: Excel import not consistently recognising blank first column

poinfrettia — Fri, 16 May 2025 08:23:36 GMT

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!

Re: Excel import not consistently recognising blank first column

Anonymous — Fri, 16 May 2025 12:06:48 GMT

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

Re: Excel import not consistently recognising blank first column

Ehren — Fri, 16 May 2025 18:16:21 GMT

Can you try setting InferSheetDimensions to true instead of false? The default is false, so setting it to false won't change anything.

Re: Excel import not consistently recognising blank first column

poinfrettia — Mon, 19 May 2025 08:05:14 GMT

Ah it was already true, I thought that was the default? It seems like neither way changes it regardless unfortunately.

Re: Excel import not consistently recognising blank first column

Ehren — Mon, 19 May 2025 19:04:48 GMT

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.