cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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,

'

3 REPLIES 3
Highlighted
Microsoft
Microsoft

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

Hi @Daltz ,

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-was...

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

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

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"

 

Highlighted
Frequent Visitor

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

Anyone solved this? I have the same issue.

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

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors
Top Kudoed Authors