Reply
Advisor
Posts: 307
Registered: ‎01-16-2018
Accepted Solution

How to run POST request in M?

[ Edited ]

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

 

 


Accepted Solutions
Super User
Posts: 1,620
Registered: ‎09-06-2015

Re: How to run POST request in M?

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: 

image.png

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Highlighted
Advisor
Posts: 307
Registered: ‎01-16-2018

Re: How to run POST request in M?

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

View solution in original post


All Replies
Super User
Posts: 1,620
Registered: ‎09-06-2015

Re: How to run POST request in M?

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...

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Advisor
Posts: 307
Registered: ‎01-16-2018

Re: How to run POST request in M?

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

Super User
Posts: 1,620
Registered: ‎09-06-2015

Re: How to run POST request in M?

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: 

image.png

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Highlighted
Advisor
Posts: 307
Registered: ‎01-16-2018

Re: How to run POST request in M?

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

Super User
Posts: 1,620
Registered: ‎09-06-2015

Re: How to run POST request in M?

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Advisor
Posts: 307
Registered: ‎01-16-2018

Re: How to run POST request in M?

[ Edited ]

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