The column 'ID' of the table wasn't found.. The exception was raised by the IDbCommand interface.

[Anonymous]

Hi,

My issue is with sheduling a refresh using a sharepoint list dataset,

So my sharepoint list has 3 columns, labelled 'ClientID', 'ClientName', 'ClientGroup'. When I connect to the sharepoint list through Power BI it does the following:

1. Pulls through all the additional columns like editedby, modifieddate, authorid etc (Including 2 columns both labelled ID)
2. Renames 2 of my column headers so 'ClientID' => 'OData_ID1', 'ClientName' => 'Title',
3. Renames one of the ID columns (mentioned in step 1) from 'ID' to 'ID.1'

All I need is my original data format, so the 3 columns with my column headers, I've tried so many different variations of steps to try and rename/remove/duplicate columns.

However once the report is published and I try to refresh from the online service, I get an error saying it can't find the column labelled either 'ID' or 'Title' (I don't understand how pbi desktop adds all these columns to my dataset, but then says it can't find them when refreshing from the online service)

The report refreshes fine in desktop, so there's no typo's or anything in my M query code, there must be something different about the online service refresh, anyone able to explain and let me know the work around?

Thanks in advanced,

'

[v-danhe-msft]

Hi @Anonymous ,

Based on my research, I suggest you open your advanced editor in query editor to modify your column, you could refer to below link that may have the same issue with you:

https://community.powerbi.com/t5/Desktop/Expression-Error-The-column-quot-xxxx-quot-of-the-table-wasn-t/td-p/9220

Regards,

Daniel He

[Anonymous]

Hi Daniel,

How do you suggest I modify the column? 

I'll paste an example of my M query here (I've replaced any links/ids), but there isn't any typo's or anything as the refresh works fine through desktop. I've rebuilt my query from scratch a few times, so there's no sort of referencing a deleted column

I've also tried plenty of different ways of scripting this, so referencing the "FieldValuesAsText" column or just referencing the 3 original column headers, i've changed up the order to rename columns last or ignore the multiple ID columns

let
    Source = SharePoint.Tables("https://xxx.sharepoint.com/sites/xxx", [ApiVersion = 15]),
    #"xxx" = Source{[Id="xxx"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"xxx",{{"ID", "ID.1"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"FieldValuesAsText"}),
    #"Expanded FieldValuesAsText" = Table.ExpandRecordColumn(#"Removed Other Columns", "FieldValuesAsText", {"Title", "OData__x0049_D1", "ClientGroup"}, {"Title", "OData__x0049_D1", "ClientGroup"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded FieldValuesAsText",{{"Title", "ClientName"}, {"OData__x0049_D1", "ClientID"}})
in
    #"Renamed Columns1"

[johanjsc]

Anyone solved this? I have the same issue.

Works fine in PBI Desktop, but not in the PBI Service with Scheduled nor manuel refresh