Script to Pull Report into PowerBI
Hi All -
Wanted to share a script that I built to help with pulling in data directly into PowerBI. Note - you do not need Python or Postman. Just add to a blank query in PowerBI and update accordingly.
let
// Get the bearer token
auth_url = "https://api.app.wdesk.com/iam/v1/oauth2/token",
client_id = "insert client_id here between quotes",
client_secret = " insert client_secret here between quotes ",
body = "grant_type=client_credentials&client_id=" & client_id & "&client_secret=" & client_secret,
auth_headers = [#"Content-Type"= "application/x-www-form-urlencoded"],
auth_response = Web.Contents(auth_url, [Headers=auth_headers, Content=Text.ToBinary(body)]),
auth_json = Json.Document(auth_response),
access_token = auth_json[access_token],
// Set the parameters for the API request
wdesk_base_url = "https://api.app.wdesk.com/platform/v1/graph/",
report_id = "insert report_id here between quotes ",
report_format = "/csv",
url = wdesk_base_url & "reports/" & report_id & report_format,
// Make the API request using the bearer token
api_headers = [
Authorization = "Bearer " & access_token,
Accept = "text/csv"
],
response = Web.Contents(url, [Headers=api_headers]),
// Convert the response to a CSV table
csv = Csv.Document(response,[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(csv, [PromoteAllScalars=true])
in
#"Promoted Headers"
-
Amazing Danielle Okland, thank you for sharing this!
0
This is great! Thank you for taking the time to share.
0
I would like to echo the others and say: Amazing stuff Danielle! Thanks for taking the time to share :-)
Second, I wanted to build upon Danielle's awesome work to make Workiva API's even more approachable. Specifically: the code snippet above assumes the API response is text/csv (through adding the header 'Accept = "text/csv"'). However, in a recent case of mine the response came over in JSON regardless. In these cases, the response can parsed in PowerBi as presented below.
response = Json.Document(Web.Contents(url, [Headers=api_headers])),
data = response[data],
// Convert the response to a table
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"responseProperty1", "responseProperty2"}, {"RenameResponseProperty1", "RenameResponseProperty2"})
// Note that Workiva API's can return lists/records that require further expansion in PowerBi. This can be achieved through the Power Query Editor.
in
#"Expanded Column1"1请先登录再写评论。
评论
3 条评论