will not update data

[AndrejZevzikov]

Hi, Guys!

I have the issue with dataset refresh in power Bi service.
Situatio is next: I using gateway to connect my pc to connect mysql database and we deleted or rename one of the column in in rone of the tables id DB. And then refresh in Power Bi service dosent work error saying that missing column "X". But in power Bi desktop I can refreshed report publish again and its workes perfectly then. So maybe there is some way to make dataset refreshed directy in power Bi service then column in DB changed, except going to power Bi desktop.

[v-xiaoyan-msft · Accepted solution]

Hi @AndrejZevzikov ,

Whenever you refresh your data, Power BI must query the underlying data source. The entire refresh process consists of multiple stages, depending on the storage model of your dataset.

Power BI does not import data through a connection that operates in DirectQuery/LiveConnect mode. Instead, whenever a report or dashboard queries a data set, the data set returns results from the underlying data source. power BI transforms the query and forwards it to the data source.

Power BI imports the data from the original data sources into the dataset. Power BI report and dashboard queries submitted to the dataset return results from the imported tables and columns. You might consider such a dataset a point-in-time copy. Because Power BI copies the data, you must refresh the dataset to fetch changes from the underlying data sources.

So when you delete or rename a column in the id DB of one of the tables. You need to reconnect to the desktop and republish, this is by design.

For more information, please see:Data refresh in Power BI 

Hope it helps,

Community Support Team _ Caitlyn Yan

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

[AndrejZevzikov]

Correct me if I wrong, 
if i created my report (in power Bi desktop) with DirectQuery option I will fix my issue, but i know (i could be wrong) if report cretaed with DirectQuery options and have some complex calculations it can have some issues with calculations results.

[v-xiaoyan-msft]

Hi @AndrejZevzikov ,

Sorry I may not have explained it clearly. Switching storage modes does not solve this issue. The suggestion above means that no matter what storage mode you use, the refresh will go to the data source to find a matchable item based on the metadata (such as column name, etc.) used by PBIX , and when this item is deleted, it cannot be found, so an error will be reported, in which case the intelligent choice is to republish or restore the deleted column.

Also, you are right, there are indeed some limitations when using direct query, for details please check:

Limitations of using Direct Query 

Hope it helps,

Community Support Team _ Caitlyn Yan

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

[tctrout]

I am very interested if anyone can explain this.

I have experienced the same issue, with a work around, but still do not know why this occurs or how to fix it without a work around.

What we had tried without success:
1. Deleted the dataset and report. Re published

2. Deleted the  datasource connection cache within the PBIX

3. Restarted the capacity

What worked for us:

1.  We added the deleted column back in source. 

[tctrout]

I finally figured out a work around for this as opposed to adding the deleted column back to the source.

In the Power Query Editor, go to the Navigation step of the impacted table.  Choose any other table other than the one that is impacted & click OK.  Go back to the Navigation and pick the table that you actually need and was impacted. 

This appears to reset the previous cache it was hung up on.

[AndrejZevzikov]

It's look like very unefficent solution ( I hope there is other way to solve this.