cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

DataSource.Error: Invalid column name

Hi, this is quite an annoying error...

have made some changes to the data source (in my case  SQL view)

have edited my reports and all works fine

but now when I open 'Power Query Editor' I get this error:

 

DataSource.Error: Microsoft SQL: Invalid column name 'MyCol'.
Details:
DataSourceKind=SQL
DataSourcePath=MySqlServer
Message=Invalid column name 'MyCol'.
Number=207
Class=16

 

of course  MyCol is no longer needed, but I cannot tell the query editor to ignore that or to refresh.

It seems that the only option is to delete the source and then re-import it, that would mean a lot of lost work (for example calculated columns).

 

is there any workaround for this problem?

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: DataSource.Error: Invalid column name

Hi @Anonymous 

I make a simple test as below: "import/ direct query" all works for following steps

i connect to SQL with Power BI Desktop,

then i delete one column from the data source,

after clicking "refresh" button on "Home" menu and "Refresh all" inside the "Edit queries",

it works and show the lastest table with one column disappeared in Power BI.

 

"

step1: have made some changes to the data source (in my case  SQL view)   

        (delete a column? change the column name?)

 

step2: have edited my reports and all works fine             

         (do you refresh from Power BI after step1?

          if you add the column which was deleted in step1 to a visual, does it give any error?) 
"

Could you show some details what you do for the steps above so i can reproduce your problem?

 

Best Regards

Maggie

 

 

 

View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

Re: DataSource.Error: Invalid column name

Hi @Anonymous 

I make a simple test as below: "import/ direct query" all works for following steps

i connect to SQL with Power BI Desktop,

then i delete one column from the data source,

after clicking "refresh" button on "Home" menu and "Refresh all" inside the "Edit queries",

it works and show the lastest table with one column disappeared in Power BI.

 

"

step1: have made some changes to the data source (in my case  SQL view)   

        (delete a column? change the column name?)

 

step2: have edited my reports and all works fine             

         (do you refresh from Power BI after step1?

          if you add the column which was deleted in step1 to a visual, does it give any error?) 
"

Could you show some details what you do for the steps above so i can reproduce your problem?

 

Best Regards

Maggie

 

 

 

View solution in original post

Anonymous
Not applicable

Re: DataSource.Error: Invalid column name

Hi, i used refresh after deleting the column, still got the error...

is there any difference between the "refresh" on the main window and the "refresh all" on Query Editor window?

 

I ended up adding a dummy null column to my view with the same name to avoid recreating all.

but of course this is a some what crooked solution

Axit Frequent Visitor
Frequent Visitor

Re: DataSource.Error: Invalid column name

The easiest way to fix this is just to refresh the preview.

 

So go Edit Queries>Refresh Preview

Subhraz Frequent Visitor
Frequent Visitor

Re: DataSource.Error: Invalid column name

Hi,

 

I faced the similar error and here's what I did.

1) Take a backup of your pbix file.

2) Go to Edit Queries and delete the dataset. This would take off your report and would show fix it. Let it be that way.

3) If it's a SQL view you drop and recreate it.

4) Get your dataset again. This would automaticlly bring back your report.

5) Go to Edit Queries and you can see that the error is no longer there.

 

Thanks,

Subhraz

wildert Frequent Visitor
Frequent Visitor

Re: DataSource.Error: Invalid column name

If its SSAS Tabular in visual studio...

 

1 - copy out the M query from the advanced tab, something like 

let
Source = #"SQL/dn1dwhost01d;datawarehouse",
powerbi_Transactions = Source{[Schema="powerbi",Item="Transactions"]}[Data]
in
powerbi_Transactions

 

2 - Delete the query and create a new query

3 - Add the M query in the advanced tab

4 - Refresh Preview

 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 50 members 904 guests
Please welcome our newest community members: