Incremental refresh - Expression.Error: The column '' " of the table wasn't found

[ponczula]

Dear community,

couldn't find an answer for my question in the entire depths of Internet so perhaps here I will find at least some tip how to move on in my case.

Problem statement:

My database created in Power BI desktop (out of several CSV files stored on sharepoint) amounts to ~20M rows. Each update (on a weekly basis) adds up another ~1M rows. Manual updates via Desktop & subsequent publishing into Service takes more & more time, so thought that an incremental refresh might sort out my issue.

Issue:

Followed the procedure as illusterd by Radacad - LINK , yet facing an error: "Expression.Error: The column '' " of the table wasn't found". Here I'm literally facing the wall. Power BI Service claims that one of the columns wasn't found, which indeed was deleted in Desktop version at some point on purpose (don't need it so deleted it making sure it won't affect further steps). Problem is that Dektop updates runs smoothly, no issues at all whereas any attempt of refresgin the report via Service (auto refresh) throws up an error as stated above.

Any words of wisdom? What am I doing wrong? 😕

[otravers]

Did you republish your dataset to the service *after* you deleted the " " column?

[ponczula]

Of course, couple of times already... Found out though that once I turn off "incremental refresh" then Service works just fine (fully refresh the dataset without any problem). When I turn it on again then the whole story repeats itself again...

[otravers]

I wonder if there's somehow an old partition with the deleted column still in the system. Can you create a new workspace to publish the dataset and test incremental refresh anew (i.e. somewhere where the dataset never existed)?

[ponczula]

I did try this, same effect... Only working solution is turning off incremental refresh... 

[otravers]

Can you show your incremental refresh settings from Power BI Desktop? Have you revised your M code to rule out that you don't have a reference to column " " left somewhere?

[ponczula · Accepted solution]

Managed to finally sort out my issue by testing multiple options in my dataset, eventually found out what was driving the error!

As I said previously - I'm loading my CVS files from folder location on SharePoint via command "Get data" > "Folder". In such scenario Power BI create a sub-folder section called "Transform File" where in the "Transform Sample File" section once can apply steps that will be applicable for all the files in the location. 

In my case CSV files (depending on the creation date) have a different structure, thus I have applied all the transformation steps in the finaly query, not in the "Transform  Sample File". However, incremental refresh for some reason points out also to "Transform Sample File" where only available step was "Source". Since there was no "Promoted Headers" step there Power BI kept throwing above errors about not finding specific column. 

The solution then is fairly simple: I have applied "Promoted Headers" step in "Transform Sample File" section and subsequently removed this step in my main query. Works like a charm now! Hopefully this will help other users too!:)

[otravers]

I really don't like this mess that the connector creates by default and always get rid of that cruft to use clean functions instead. Here's a good video about it (with SharePoint.Files, I tend to use SharePoint.Contents but the principle remains that the helper queries and sample files are not needed):

https://www.youtube.com/watch?v=3GIz50pftZ0

[ponczula]

Never seen that, will take a look definitely.

Apart from the above one more question pops up in my mind: is there any workaround not to overwrite partitions created by incremental refresh every time I need to re-upload my pbix file? Say I need to add some visualizations or add some slicers or whatever - need to work on it it pbix and subsequently publish again into service which clearly erase exisitng partitions. Is there any way to keep them "untouched"? 

[v-mengzhu-msft]

Hi @ponczula ，

I understand what you are trying to achieve, you don't want some of the original partitions to be affected by your republish.

However, I regret to inform you that when a report with incremental refreshes is configured to be published to the service, the same model cannot be republished from Power BI Desktop. Re-publishing will delete any existing partitions and data already in the dataset. This is the limitation point for incremental refreshes.  

You can refer this doc to get more information: Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Docs

That is, there may not be an equivalent way to leave the existing partitions “untouched”.

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly