cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zoloturu
Advisor

How to run POST request in M?

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

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III
Super User III

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

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

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

zoloturu
Advisor

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

6 REPLIES 6
Super User III
Super User III

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

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

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




zoloturu
Advisor

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 III
Super User III

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

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

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

zoloturu
Advisor

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

Super User III
Super User III

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

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

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




zoloturu
Advisor

Re: How to run POST request in M?

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

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors