Changing parameter in Service leads to missing column error for unused column

[MAVIE]

I have created a report which get data from different SharePoint sites.
Which SharePoint site is used, is defined by a parameter in the report. 

When I try changing that parameter in Power BI Service to use a different SharePoint site, the refresh fails with a missing column error despite the mentioned column never being used.

Example below: 

In this example the error occurs on the "Team LeadId" column, despite the fact that the column is never used in any way or referenced in the Power Query. (Note: Changing the paramater works fine in Power BI Desktop).

Has anyone experience this issue, and found a resolution to it? 

[lbendlin]

despite the fact that the column is never used in any way

Please double check. Might be as innocent as a mention in Table.RemoveColumns.

[MAVIE]

Hi @lbendlin 

I am completely sure. This report is run on many different SharePoint sites, and the columns that fail are different depending on the site that it was loaded on before it was published. 

My suspission is that Power BI Service saves some metadata about which columns are in the report, (even if they are not used) and that this is the reason why changing which SharePoint site is used fails (in the Service only). Though I do not know for sure if this is the reason.

[lbendlin]

Ah, i missed the SharePoint part. Your lists likely are too wide (too many columns) and Power BI is randomly dropping columns.

[MAVIE]

Oh interesting, the tables can indeed become really wide as the power query is quite flexible with what is accepted. I haven't noticed dropped columns in the desktop, but are you suggesting that unused columns might be dropped from the datasets when uploaded to the service? 

[lbendlin]

I didn't say "unused" - I said "random"...

[MAVIE]

Alright. Well are you aware of any solution to this issue?

[lbendlin · Accepted solution]

Use the v2 connector and specify a view that has fewer columns. Generally have fewer columns. Use a more robust data store like SQL server.