Forum Discussion
Power Bi REST API update row values in dataset
Hi all,
I have created a dataset via API called Shipment. Each row is one shipment.
It is well explained in the MS Learn Center how to add new Rows. But how to update row details? For example I have column called "customer ref". I would need to change this reference. How to do this?
There is no direct endpoint for this. So I was exploring /executeQueries endpoint but I am still stuck with DAX Query.
The obivious way would be to add new record and then in my calculations always use the latest record but this will lead massive datasets and complex Queries which I do not want.
Is there a way to change Shipments[Customer ref] value via API without creating new record/row.
Many thanks!
Ulari
Ulari Updating a row on Power BI dataset is currenlty not possible. Try the recommended approach below
1. Change your source to an table in a excel file and store the file in sharepoint.
2. Pull the data from sharepoint into Power BI.
3. Now with the help of power automate you can update, delete or add rows to the online excel and then refresh the Power BI dataset.
You can also query the dataset using the Execute Queries connector available in Power Automate and will only be needing power automate free license.
Did I answer your question ? Please mark this post as a solution.
Thanks,
Jai
7 Replies
- Jai-Rathinavel
Super User
Ulari Updating a row on Power BI dataset is currenlty not possible. Try the recommended approach below
1. Change your source to an table in a excel file and store the file in sharepoint.
2. Pull the data from sharepoint into Power BI.
3. Now with the help of power automate you can update, delete or add rows to the online excel and then refresh the Power BI dataset.
You can also query the dataset using the Execute Queries connector available in Power Automate and will only be needing power automate free license.
Did I answer your question ? Please mark this post as a solution.
Thanks,
Jai
Thanks you! This is exactly what I needed to understand. I am disappointed of course that there is now way of updateing the rows directly in Power BI but good at least that I did get the confirmation.
My only concern now is that I will be running out of lines in Excel spreadsheet (Max 1M lines) over time and need to set up a logic to add new files when this happens.
Will look into this setup anyways - thanks once more!
- Jai-Rathinavel
Ulari You can use dataverse table instead of a excel table but you would be required to purchase premium license. You can use the update row / add row connector for dataverse within the power automate flow. Dataverse tables support direct query too !
Did I answer question ? Please mark this post as a solution.
Thanks,
Jai
- Expiscornovus
Most Valuable Professional
Hi Ulari,
Like you mentioned already, it seems we don't have a updaterows method. It has been listed as an idea for a while though (which you can upvote)
https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=28ebb38e-0b1f-4244-a1ea-ac31996ae3f5&%3B%3B
Regarding a workaround, you might be able to use a new column for this (calculated). Can you try a DAX query like the below in your exectureQueries endpoint?
UpdatedCustomerRef = IF(Shipments[ShipmentID] = 123, "NewCustomerRefValue", Shipments[CustomerRef])https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-calculated-columns
Thanks for replying!
May I use your kind help a little further?
I am struggeling with implementing the request body.
Can you point me to correct direction?
And even further - would it be possible to over-write the original value instead adding new column? This customer ref field was just an example. Basically any field may change.
Thanks
Ülari
Hi Ulari ,
As far as I know, Power BI is a read-only application to create report and analysis data. Power BI currently support us to execute dax queries for dataset by REST API.
Datasets - Execute Queries - REST API (Power BI Power BI REST APIs) | Microsoft Learn
Your new requirement sounds like replace function. You can try replace in Power Query Editor in Power BI Desktop.
It doesn't support us to call it by REST API.
Replace 123's original CustomerRef A to NewCustomerRef
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
