Forum Discussion
Columns missing from imported Excel data
Community ChampionHello NJS0303
I know this problem by myself.
when you read data with Excel.Workbook and read a table (sheet) ALWAYS the USEDRANGE of this sheet is read.
So, ensure that all your sheets/file have the same usedrange set (starting always at ColumnA and ends on the same column - otherwise not dinamically applyd functions will crash). This will solve your problem
Have fun
Jimmy
Hi Jimmy801,
It's reassuring to hear that I'm not the only person having this issue and appreciate you taking the time to respond.
I've done some more research on UsedRange but I haven't worked out how to apply this in my particular case. Is there an adjustment to be made within Power BI's Advanced Editor or do I have to do this at the Excel side? If it's the former would be able to provide an example using my sample code?
- Jimmy801
hello
this hasn't to do anything wtih power query
this is only a matter of settings in Excel.
The USEDRANGE is a read-only object that is determed by excel, if a range is used.
I will write you a VBA code to read this object. You can paste this to your files and check whether a the usedranges are the same. In addition is always dangerous to use an non dynamically approach when working with an unstructered data like Excel Table.RemoveColumns(Table, {"Column16"}) can lead to an unexpected result, as Column16 may not exist or not contain the data you are expecting (as you saw in your example, the usedranges may not be identically, even you have designed it like that - maybe used changed something)
Here now the VBA code to check
sub checkUsedRange ()
dim ws as worksheet
for each ws in thisworkbook.worksheets
debug.print ws.name & " - " & ws.usedrange.address
next ws
end sub
have fun
Jimmy
That's fantastic Jimmy801, thanks for taking the time to do that.
I've checked the UsedRange of my example spreadsheet and it's reporting A1:D18822 which is correct but Power BI is essentially importing A1:C18822. I'm beginning to suspect that this is being caused by the SharePoint folder connector as when I import the same spreadsheet using the standard Excel connector A:D I'm not having the missing column issue.
