SharePoint list based report will not refresh data.

[rheitrich]

Hello. 

I am new to the community and somewhat new to POWER BI. I have read anything I could find on similar issues but I cannot seem to figure out the solution. 

I have created a simple 2 column list on Sharepoint Online for the purpose of troubleshooting this issue. I have done no special formatting or set up. There are no columns that have "lookup" or "choice" options. Simply two text columns, 'Location' and 'ShortName'.

In PowerBI Desktop I connect to that data source, load the list and create a simple table visualization with Location and ShortName. (NOTE: SharePoint has many more columns including an ID column, which then gets duplicated as an ID.1 column with the same values in PowerBI). The desktop report runs fine and I can refresh the data, make changes or add more locations and after a refresh, everything works as expected.  I publish the report to our cloud powerbi.com site and the new "Locations" report and dataset show up properly.  However, when I try to refresh the data, I get the error: "The column 'ID' of the table wasn't found. Table: Locations"

I know someone posted a similar issue but when I tried to follow the solution, It wouldn't work or I couldn't figure it out. I have not changed, added, sorted, or made any other adjustment to the report by editing the query or the adjustments in the query window.

Here is the code in the Advanced Editor window:

let
Source = SharePoint.Tables("["[URL to the sharepoint site]", [ApiVersion = 15]),
#"[Source ID]" = Source{[Id="[SourceID]"]}[Items],
#"Renamed Columns" = Table.RenameColumns(#"[SourceID]",{{"ID", "ID.1"}})
in
#"Renamed Columns"

I tried to remove the ID column, remove the ID.1 column, etc. and nothing seems to work. I have no idea why I'm getting this error when it all works well when I refresh the data from the Desktop application. Any help would be super appreciated.

Thanks!

[rheitrich]

Hello. 

I am new to the community and somewhat new to POWER BI. I have read anything I could find on similar issues but I cannot seem to figure out the solution. 

I have created a simple 2 column list on Sharepoint Online for the purpose of troubleshooting this issue. I have done no special formatting or set up. There are no columns that have "lookup" or "choice" options. Simply two text columns, 'Location' and 'ShortName'.

In PowerBI Desktop I connect to that data source, load the list and create a simple table visualization with Location and ShortName. (NOTE: SharePoint has many more columns including an ID column, which then gets duplicated as an ID.1 column with the same values in PowerBI). The desktop report runs fine and I can refresh the data, make changes or add more locations and after a refresh, everything works as expected.  I publish the report to our cloud powerbi.com site and the new "Locations" report and dataset show up properly.  However, when I try to refresh the data, I get the error: "The column 'ID' of the table wasn't found. Table: Locations"

I know someone posted a similar issue but when I tried to follow the solution, It wouldn't work or I couldn't figure it out. I have not changed, added, sorted, or made any other adjustment to the report by editing the query or the adjustments in the query window.

Here is the code in the Advanced Editor window:

let
Source = SharePoint.Tables("["[URL to the sharepoint site]", [ApiVersion = 15]),
#"[Source ID]" = Source{[Id="[SourceID]"]}[Items],
#"Renamed Columns" = Table.RenameColumns(#"[SourceID]",{{"ID", "ID.1"}})
in
#"Renamed Columns"

I tried to remove the ID column, remove the ID.1 column, etc. and nothing seems to work. I have no idea why I'm getting this error when it all works well when I refresh the data from the Desktop application. Any help would be super appreciated.

Thanks!

[Anonymous]

Give this a try.  Go into your Power Query code and remove the "ID Rename" step.  Instead, select the columns you want and choose the "Remove other columns" option.  This will let you optimise your model and hopefully get around the issue.

[rheitrich]

Thanks Ross73312

I tried following your instructions. I tried removing the "ID rename" step but it generated an error. I was not sure what to do with the "in" clause as it only had the #Renamed Columns". I then did the suggested column removal step first, and then removed the "ID Rename" step but it still generates an error. 

The query now looks like this.

let
    Source = SharePoint.Tables("SiteURL", [ApiVersion = 15]),
    #"SourceID" = Source{[Id="SourceID"]}[Items],
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Title", "ShortName"})
in
    #"Removed Other Columns"

The error is:

Expression.Error: The name 'Renamed Columns' wasn't recognized.  Make sure it's spelled correctly.

Thanks again

[Anonymous · Accepted solution]

Your row called "Removed Other Columns" is still refering to the deleted step.  Most power query lines refer to a previous row.  If you deleted the row in the Applied Steps, it makes the coding updates for you.

I'd expect this to work

let
    Source = SharePoint.Tables("SiteURL", [ApiVersion = 15]),
    #"SourceID" = Source{[Id="SourceID"]}[Items],
    #"Removed Other Columns" = Table.SelectColumns(#"SourceID",{"Title", "ShortName"})
in
    #"Removed Other Columns"

[rheitrich]

Thanks so much for your help!!