Columns missing from imported Excel data

[NJS0303]

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!

[JLemoine]

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

[CJK]

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 😁

[ShankarS]

---

@CJK 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

[CJK]

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!

[ShankarS]

Thanks for the quick response, @CJK -- the SAP KB 2853508 is stating its relevant for BO 4.2 & is not reproducible above BO 4.2 Support Pack 7 Patch 500 and above versions.. But both of us seem to be facing this after updating to BO 4.3 SP1 Patch 1. 

SAP then have to clarify if they are same OR if this is a different issue. I have also raised with SAP, along with here within Microsoft as manual workaround is not practical due to the volume of reports impacted on our end.

[Jimmy801]

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

[NJS0303]

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

[NJS0303]

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.

[Jimmy801]

Hello @NJS0303 

what about your problem?

If my posts did help your or solved your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[NJS0303]

Hi @Jimmy801 

Sorry (again) for the delay in responding.

I've finally been able to identify what is causing the issue, namely there's a known issued with the SAP Web Intelligence .xlsx reports in terms of how Power BI reads them: 

https://apps.support.sap.com/sap/support/knowledge/preview/en/2853508

I don't have full access to SAP's knowledge base so I'm operating with limited information at the moment. I'll try and find out more but I'm hoping someone reading this has access to the knowledge base and can share the full article.

[Jimmy801]

Hello  @NJS0303 

we had a similar issue with xlsx-file from SAP because of an XLM-problem. Simple download xls or csv version (it would be always best to use txt or csv-files for interfaces). Then it should work

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[Jimmy801]

Hello

could you please post the file and the M-code

Thanks

Jimmy

[az38]

hi @NJS0303 

show us please, all source code from Advanced Editor window

[NJS0303]

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.