I have a report which is working and updateing data in desktop. When i hit the refresh button i connects properly to the dbs and the apis and gets all data.
When i publish it then the initial upload is succesfull but when i try to refresh the dataset manual ( so i can be sure that the autorefresh will work) i get the next issue.
I do not know what rowset is it refered here. It is on table that does not have column id?
I can not find something.
The gateways are working properly from what i see from their connections.
Can someone propose where to look or what more info is needed to check?
the issue is slightly different, the meaning is not exactly what it seems.
In reality the message id column is not found in rowset is not reffereng to a real column named id, it is referring to the some id that powerbi is reading for a specific column ( and propably every column as object lets say has an id) from the datasource.
How this was identified? I found first the table by removing them one by one. The table that I found the issue was technically a view in mysql database which I have changed lately.
The change was that I refactored 3columns. These column where calculating datediff for calendar days using the standard function of mysql db. The new calculation was about calculating working days excluding weekends. This was done by calling a function created in mysql db. The refactored column keeps the same name and type so there is not change on type and name.
So the only difference is that this column is calculated by custom function in mysql and not by standard datediff() function of mysql.
Some more info is that desktop is updating properly without any problem but service is not.
Reworking back the function to do the calendar date diff makes the update of Service working properly.
So does the powerbi service has issue with columns calculated from functions cause it can not find any “id” variable in the rowset of this column?
Do we have any workaround. As i see there are 2 cases:
1) to find how to make the service updating by what other settings is needed to be passed (which i preffer)
2) calculate working days in powerbi which i do not know exactly the algo
#1, This issue means your data table structure does not match with the latest records when you update.
For this scenario, you need to enter to query editor and check each step to manually fix these table suture changes. (power query will saving the table structure into query steps, these structures not auto-sync if you changes on the data source side)
#2, You can try to use calendar and workday functions to use date fields to calculate the workdays.
Total WorkDays = COUNTROWS ( FILTER ( CALENDAR ( Table[Start Date], Table[End Date] ), WEEKDAY ( [Date], 2 ) <= 5 ) )
If the above does not help, can you please share some dummy data to test?
for number 2 i made almost the same logic just in a new table cause i need to the same calculation for a table with hundreds of rows so it is a better way not to create everytime a calendar.
for number 1. the power query does not give any problem. an probably it does not have the same issue with Service cause it does not give any problem with a missing id variable of a column. As i said the problem was on a column that name was submit_days and this calculation was made by a relative workingdays function created in database. For this column either with this name either with whatever other name the Service tries to find an Id variable which can not be found.
In my opinion, I'd like to suggest you remove these types of fields from your query table to prevent the issue. (as I said, power query will saving table structures, so the unstable fields may cause the field recognize issues)
Try refreshing your report from Power BI desktop. If it is throwing an error in service, then definately it would not work in desktop version as well.
Also, open Power Query and see if you are getting any error on any of the transformation steps of different queries.
In desktop it is refreshing the data without issue. I also opend transformation and i refreshed every table.
The only new thing that i saw there was a "alert" that the preview is maybe closed from the options (which was not) and that i can manually refresh the preview (which i did and it works) and that this has nothing to do with data refresh
Another thing that comes to my mind is you can check your sources in the desktop and the service.
In the dataset-->settings, edit credentials, see if the dataset sources are matching with that of your desktop.
Another thing you can do as a hit and trial is, download the report from the Power BI service and open in Power BI desktop.
Then in Power Query Editor, see if the same report is showing any error.
sources and credentials are the same.
I downloaded the report and refreshed the data. it run smoothly but still refresh on service is not working
You can check the following links and see if they help
not helping i tried to download the report , update the data (successfully) save with different name and upload (again until now everything is fine) and on Service dataset refresh i get the same issue
Learn how to create your own user groups today!
Click here to read more about the November 2021 Updates!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.