Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear community,
I know that Power BI is for ETL, but I'm interested in understanding of all capabilities of it.
Did somebody send a POST request to run some action using M (Power Query)?
For instance this one - https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/refreshdatasetingroup
Regards,
Ruslan
Solved! Go to Solution.
Adding an empty string as the Content-parameter does the trick for me:
Content=Text.ToBinary("")
Although the query will then return an error. the refresh actually was successful:
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @ImkeF,
You are correct. It is working for me as well. Thank you!
And to avoid this error we need to add any extra step to return some value.
Here is the final code for this case:
let vToken = AuthorizationToken, vUrl = "https://api.powerbi.com/v1.0/myorg/groups/" & WorkspaceID & "/datasets/" & DatasetID & "/refreshes", Source = Json.Document(Web.Contents(vUrl, [Headers=[Authorization=vToken],Content=Text.ToBinary("")])), result = 1 in result
where AuthorizationToken, WorkspaceID and DatasetID are parameters.
Regards,
Ruslan
Hi @zoloturu,
I've described how this worked for me here: https://www.thebiccountant.com/2018/06/05/easy-post-requests-with-power-bi-and-power-query-using-jso...
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @ImkeF,
Thank you for this information. But my case is different.
I have two requests which are completely the same, only difference is how you run it - GET or POST:
1. Datasets - Get Refresh History In Group (GET)
https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetKey}/refreshes
https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/refreshdatasetingroup
2. Datasets - Refresh Dataset In Group (POST)
https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetKey}/refreshes
https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/getrefreshhistoryingroup
How to run the second one in Power Query?
Regards,
Ruslan
Adding an empty string as the Content-parameter does the trick for me:
Content=Text.ToBinary("")
Although the query will then return an error. the refresh actually was successful:
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thank you for this. I have been searching for this answer for 2 years!
Hi @ImkeF,
You are correct. It is working for me as well. Thank you!
And to avoid this error we need to add any extra step to return some value.
Here is the final code for this case:
let vToken = AuthorizationToken, vUrl = "https://api.powerbi.com/v1.0/myorg/groups/" & WorkspaceID & "/datasets/" & DatasetID & "/refreshes", Source = Json.Document(Web.Contents(vUrl, [Headers=[Authorization=vToken],Content=Text.ToBinary("")])), result = 1 in result
where AuthorizationToken, WorkspaceID and DatasetID are parameters.
Regards,
Ruslan
Thank you for providing the code needed to force Power BI to make "POST" requests, and deal with the error. I have been searching for this answer for 2 years!
Hi Ruslan @zoloturu,
be careful here: M is a paritally lazy language. That means that in most cases, only those steps will be evaluated who are necessary for the end result. So the refresh will actually not take place if you refresh the query from the main menu.
But it's a good step to prevent undesired refreshes during query design (there is still the limit of max. 8 refreshes within 24 hours).
So it depends on how you want to trigger the refreshes whether it makes sense to keep the last step or not.
Cheers, Imke
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi Imke @ImkeF,
It is an interesting point. During the last 2 years, I didn't have such problems with M. It will be interesting to see if you have an example of such behavior or documentation explaining that. Then it can help readers of this thread to be ready for such situations. I think if there will be such a possibility, I will try to implement some check. In this case, there can be the same GET request but without the Content part and it will return refresh history. You can make it as a function and invoke it to DatasetID. So then you can see if your refresh has proceeded.
Here is an example of such function:
let fnRefreshHistoryByDataset = (WorkspaceID as text, DatasetID as text) => let Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/groups/" & WorkspaceID & "/datasets/" & DatasetID & "/refreshes", [Headers=[Authorization=AuthorizationToken]])), value = Source[value], ToTable = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error), ExpandColumns = Table.ExpandRecordColumn(ToTable, "Column1", {"id","refreshType", "startTime", "endTime", "status"}, {"id","refreshType", "startTime", "endTime", "status"}) in ExpandColumns in fnRefreshHistoryByDataset
And yes, indeed. There are 8 refreshes during 24 hours on shared capacity and 48 refreshes when you have dedicated one (Premium or Embedded). But a question here was just to try if it is possible to run POST request to run some action.
Regards,
Ruslan
User | Count |
---|---|
124 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |