0

I'm trying to retrieve data from Power BI API services and the same is being accomplished via PowerShell code. But unfortunately due to some circumstances I am not able to deploy it in production. Hence now I am trying to get the same thing done in Power BI desktop itself so that I can call power BI rest API from Power Query only. There are tons of blogpost about calling an API in power query but they all require Power BI App registered Client ID. Which I don't have. I'm successfully able to call with my user name password in PowerShell and even I'm getting response from API.

Please find below PowerShell code and let me know if we can replicate the same in Power Query.

# User credential $User = 'shahab***@*****.com' $Pword = ConvertTo-SecureString –String '***password***' –AsPlainText -Force $Credential = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $User,$Pword # Connect to service Login-PowerBIServiceAccount -Credential $Credential #Get Bearer token $headers = Get-PowerBIAccessToken $uri = 'https://api.powerbi.com/v1.0/myorg/datasets/f52f2abc-6445-41ee-ce02-3908c6e18dd4/refreshes' $refreshes = Invoke-RestMethod -Uri $uri -Headers $headers -Method GET $xs= $refreshes

Thanks.

Improve this question
2
  • I'm confused. You have a table from powershell, but you can't deploy the results to production? Are you trying to refresh a dataset, when the powerquery is evaluated ? -- Power Query is not great on REST APIs, it's going to call the endpoint more times than you'd think because of isolating queries, and query editor previews. -- It seems like a XY question where there's other options, but I'm not totally clear on what you're doing. Commented May 30, 2021 at 17:56
  • Hi @ninMonkey Thanks for your reply. I'm trying get data from Power BI service and I'm using GET method. This is done in PowerShell and we're able successfully retrieve the records as well. same thing I want to do in Power Query using M Language Commented May 31, 2021 at 7:28

1 Answer 1

Reset to default
2

With the wrapper, you want something like this.

WebRequest_Simple( "https://api.powerbi.com", "v1.0/myorg/datasets/f52f2abc-6445-41ee-ce02-3908c6e18dd4/refreshes", [ Headers = [ Authorization = "Bearer OAuthTokenHere" ] ] )

If the url had a query string (the part after ?), then you'd use options[Query]

Web Response

enter image description here

Full Query

Source: WebRequest_Simple.pq

let /* Wrapper for Web.Contents returns response metadata for options, see: <https://learn.microsoft.com/en-us/powerquery-m/web-contents#__toc360793395> */ WebRequest_Simple = ( base_url as text, optional relative_path as nullable text, optional options as nullable record ) as record => let headers = options[Headers]?, //or: ?? [ Accept = "application/json" ], merged_options = [ Query = options[Query]?, RelativePath = relative_path, ManualStatusHandling = options[ManualStatusHandling]? ?? { 400, 404 }, Headers = headers ], bytes = Web.Contents(base_url, merged_options), response = Binary.Buffer(bytes), response_metadata = Value.Metadata( bytes ), status_code = response_metadata[Response.Status]?, json = Json.Document(response), Final = [ request_url = metadata[Content.Uri](), status_code = status_code, metadata = response_metadata, IsJson = not (try json)[HasError], response = response, json = json ] in Final, tests = { WebRequest_Simple("https://httpbin.org", "json"), // expect: json WebRequest_Simple("https://www.google.com"), // expect: html WebRequest_Simple( "https://api.powerbi.com", "v1.0/myorg/datasets/f52f2abc-6445-41ee-ce02-3908c6e18dd4/refreshes", [ Headers = [ Authorization = "Bearer OAuthTokenHere" ] ] ) }, FinalResults = Table.FromRecords(tests, type table[ status_code = text, request_url = text, metadata = record, response = binary, IsJson = logical, json = any], MissingField.Error ) in FinalResults
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.