19.11.2020
05:05
Hi everyone,
is someone here how understands power query? I got a task to retrieve data from a BPS report in excel to use it for additional analysis.
I was able to retrieve the data but transforming the json response to a table is beyond me.
Just in case there's someone who would be able to do it but doesn't know how to retrieve the data I add the logic below which can be put into the advanced editor.
You need to create an APP with impersonation. For some reason the report was returned but without rows without impersonation. I even granted the APP business administrator privileges without success. Using impersonation worked though.
Regards,
Daniel
let
api_url = "http://*.cosmoconsult.com",
token_path = "/api/login",
Content = "{'clientId':'4c01c4fb-9b82-4912-b410-5e54ef352e2b','clientSecret':'*','impersonation': { 'login': '*' } }",
Token_Response = Json.Document(Web.Contents(api_url,
[
RelativePath = token_path,
Headers=[#"Accept"="application/plain", #"Content-Type"="application/json-patch+json;charset=UTF-8"],
Content=Text.ToBinary(Content)
]
)
),
access_token = Token_Response[token],
AccessTokenHeader = "Bearer " & access_token,
data = Json.Document(Web.Contents(api_url, [RelativePath="/api/data/v2.0/db/1/applications/34/reports/79", Headers=[accept="application/json", #"Content- Type"="application/json", Authorization=AccessTokenHeader]])),
#"Converted to Table" = Record.ToTable(data),
in
#"Converted to Table"
