Data source error The column 'XXX' does not exist in the rowset - weirdest bug?

[Anonymous]

Hi folks,

I just had the weirdest bug that took me some time to figure out. I uploaded a PowerBI report to the cloud and everytime the data was to be refreshed, I would get the infamous error "Data source error The column 'xxx' does not exist in the rowset" where xxx is a column name.

The report worked and refreshed just fine in PowerBI Desktop, no errors thrown!

Of course, I went through all! my PowerQuery "m code" (mind there is no CTRL+F to just search through all queries so I had to manually open each table and check the avanced editor) to see if the column in question was referenced anywhere - it wasn't.

Turns out, I had to manually refresh the report in the report editor as well (as opposed to just updating queries and closing+applying) in PowerBI desktop before uploading it.

Thought I'd let you know... (inb4 "It's a feature, not a bug").

Cheers

[Anonymous · Accepted solution]

Thanks for posting additional information here. I know about these types of "problems" when referencing a column by name in m script / PowerQuery but in my specific case this was not the issue.

As I wrote in my original post, the issue was literally that I didn't refresh the report in the report editor before uploading it and after I did, everything was fine. You can check this when you view the model and the "old" columns are still visibile (I never tried to uploading without replacing though, but I doubt this would have solved anything).

Again, thanks for putting more references here. However, my issue was a different one for which I didn't find a solution documented anywhere so I posted it.

[v-yiruan-msft]

Hi @Anonymous ,

Based on the error message, it seems to be caused by a report referencing a column that no longer exists in the data source. But you mentioned that the column is not referenced anywhere in the report. Please try to delete the related report in Power BI Service first, and publish the report from Power BI Desktop again. Later check if it can work well.

In additon, please review the following links which has the similar problem with yours, hope they can help you resolve the problem.

Refresh error: The xxxx column does not exist in the rowset

---

Scenario1: When you modify the data structure in the data source, the refresh error occurs.
Scenario2: When you have pivot columns steps in query editor, the value in the data source has been modified, the refresh error occurs.

---

The column does not exist in the rowset

---

After stabbing buttons in the desktop app, I managed to refresh the query and republish the report.  I was then a little panicked to see that thee of the four charts in the report were showing as in error.  I traced this to a custom field in the report which identifies the creation month from the creation date.  (It made sense that this field had been dropped, as it is not in the original data set.)  Once I'd added this back in to each chart, the report functions correctly again.

---

The 'HasUniqueRoleAssignments' column does not exist in the rowset.

---

In order to avoid referencing the columns that are magically put into the report, use the "Remove Other Columns" feature in the Query Editor.  If you select the ABSOLUTE minimum number of columns you are using for your report across ALL data sets synced, (ctrl + shift + click) to select only the columns you are using... and then right click and select remove other columns the steps reference only the used columns.  

Thus you get something like this: 

#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns1",{"Column 1", "Column 2", "Column 3", "Column 3")

instead of:

  #"Removed Columns" = Table.RemoveColumns(#"Removed Other Columns",{"ERROR CAUSING COLUMN 1". "ERROR CAUSING COLUMN 2". "ERROR CAUSING COLUMN 3". "ERROR CAUSING COLUMN 4". })

Once you remove them with this method, the Web application does not know that the bad columns exsist and completes a full refresh.  

It's complete bull**bleep** and i have multiple reports with 5-10 data sources with 30+ columns each.... but it works.  And this is a total work stopper for my org. 

---

Best Regards