Forum Discussion
Columns missing from imported Excel data
Hello,
Looking for some assistance with an issue I'm having with imported Excel data.
I've got several reports linked to approximately a dozen different excel sheets and without any obvious cause some of the sheets are missing columns when imported into Power BI. As an example, one file has 17 columns but once imported the final column is missing and fails with an Expression.Error: 'column 17' of the table wasn't found:
Source = Excel.Workbook(File.Contents("\\absvapp036c\itrent\001 - ESS Mileage.xlsx"), null, true),
#"001 - ESS Mileage - Claim_Sheet" = Source{[Item="001 - ESS Mileage - Claim",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"001 - ESS Mileage - Claim_Sheet",{{"Column1", type any}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type text}, {"Column9", type any}, {"Column10", type text}, {"Column11", type any}, {"Column12", type text}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type text}}),
I've been adding blank columns into the source reports to try and workaround this but this is not an ideal solution as it doesn't always work and I'm now concerned that the import query is also missing other data.
I've been around the internet trying to find a fix for this and I've tried all of the solutions I could find, e.g. refresh cache, refresh preview, create new query etc. but nothing seems to stop a column from being ignored by the import process. Should/could I be using M directly to extract all columns rather than relying on the editor and if so, can anyone help with the code to use?
Any help/advice would be greatly appreciated!
16 Replies
Jimmy801Community Champion
Hello 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
Hey There,
I know this is an older post/issue but i've recently run into the same problem as my company has upgraded our SAP BO program to:
SAP BusinessObjects BI Platform 4.3 Support Pack 3 Patch 1 Version : 14.3.3.4496.1
I've found that adding an additional column on the right-most side of your SAP BO report seems to resolve the issue (essentially, the read error is happening on the right-most "TEST" column, but no issues with the rest of my data now)
This resolution is definitely not as intuitive or technically sound as most of the previous answers but, it has worked for me 😁
Anonymous wrote:Hey There,
I know this is an older post/issue but i've recently run into the same problem as my company has upgraded our SAP BO program to:
SAP BusinessObjects BI Platform 4.3 Support Pack 3 Patch 1 Version : 14.3.3.4496.1
I've found that adding an additional column on the right-most side of your SAP BO report seems to resolve the issue (essentially, the read error is happening on the right-most "TEST" column, but no issues with the rest of my data now)
This resolution is definitely not as intuitive or technically sound as most of the previous answers but, it has worked for me 😁
Hi - Has this been already raised with Power-BI team for fixing the Connector OR with the SAP team if its a problem with the BO patch issue? Were you able to find a permanent resolution to this?
We just updated our environment to this version and face the same issue - so wanted to confirm if we can piggy back on the issue you may have raised with Microsoft / SAP OR if we need to start afresh.
SAP BusinessObjects BI Platform 4.3 Support Pack 3 Patch 1 Version : 14.3.3.4496.1
Hey ShankarS
SAP has acknowledged this issue via a knowledge article:
https://userapps.support.sap.com/sap/support/knowledge/en/2853508
Have not seen/heard anything in reference to this issue since i found it a few months back - sorry i am not much help!
Hi az38 ,
Apologies for the delay in responding - please see below for the information requested:
let Source = SharePoint.Files("https://redacted.sharepoint.com/sites/MidlandTransfer", [ApiVersion = 15]), #"005-UserStructure_ITRENT-PBI xlsx_https://redacted sharepoint com/sites/MidlandTransfer/Shared Documents/ITRENT-PBI/" = Source{[Name="005-UserStructure_ITRENT-PBI.xlsx",#"Folder Path"="https://redacted.sharepoint.com/sites/MidlandTransfer/Shared Documents/ITRENT-PBI/"]}[Content], #"Imported Excel" = Excel.Workbook(#"005-UserStructure_ITRENT-PBI xlsx_https://redacted sharepoint com/sites/MidlandTransfer/Shared Documents/ITRENT-PBI/"), UserStructure_Sheet = #"Imported Excel"{[Item="UserStructure",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(UserStructure_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"User Login", type text}, {"Level3:Structure", type text}, {"Level4:Structure", type text}}) in #"Changed Type".
This example is taken from a 4 column Excel file which is imported into Power BI with the first 3 columns only.
Hi, for your Information the following SAP Note is applicable for your issue:
3336825 - Excel output generated by Web Intelligence is not working properly in PowerBI
Thnaks,
Julien
I know this is an old thread but we are having the same issue with SAP BO generated Excel file as well and what fixed it is adding
InferSheetDimensions = true when calling the Excel.Workbook function.
Excel.Workbook([Content],[DelayTypes = true, InferSheetDimensions = true]))So sample code would be:
= Excel.Workbook(File.Contents(\\PathToFile\ExcelFile.xlsx"),[DelayTypes = true, InferSheetDimensions = true])
