Forum Discussion
Running Rest API in Power query on each object
Hi smpa01
Based on my test, we don't need to run separate queries with generating separate Bearer tokens. Below are my codes:
Custom function: GetDatasources
(dataflowId as text) => let Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/groups/xxxxgroupidxxxx/dataflows/"&dataflowId&"/datasources", [Headers=[Authorization="Bearer xxxxxxtokenxxxxx"]])), #"Converted to Table" = Table.FromRecords({Source}), #"Expanded value" = Table.ExpandListColumn(#"Converted to Table", "value"), #"Changed Type" = Table.TransformColumnTypes(#"Expanded value",{{"@odata.context", type text}, {"value", type any}}) in #"Changed Type"Result query
let Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/groups/xxxxxxgroupidxxxx/dataflows", [Headers=[Authorization="Bearer xxxxxxxxxtokenxxxxxxxxxxx"]])), #"Converted to Table" = Table.FromRecords({Source}), #"Expanded value" = Table.ExpandListColumn(#"Converted to Table", "value"), #"Expanded value1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"objectId", "name", "description", "configuredBy", "users"}, {"value.objectId", "value.name", "value.description", "value.configuredBy", "value.users"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded value1",{{"@odata.context", type text}, {"value.objectId", type text}, {"value.name", type text}, {"value.description", type any}, {"value.configuredBy", type text}, {"value.users", type any}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"value.objectId", "value.name"}), #"Invoked Custom Function" = Table.AddColumn(#"Removed Other Columns", "datasources", each GetDatasources([value.objectId])) in #"Invoked Custom Function"Result
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Hi smpa01
Based on my test, we don't need to run separate queries with generating separate Bearer tokens. Below are my codes:
Custom function: GetDatasources
(dataflowId as text) =>
let
Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/groups/xxxxgroupidxxxx/dataflows/"&dataflowId&"/datasources", [Headers=[Authorization="Bearer xxxxxxtokenxxxxx"]])),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded value" = Table.ExpandListColumn(#"Converted to Table", "value"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded value",{{"@odata.context", type text}, {"value", type any}})
in
#"Changed Type"
Result query
let
Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/groups/xxxxxxgroupidxxxx/dataflows", [Headers=[Authorization="Bearer xxxxxxxxxtokenxxxxxxxxxxx"]])),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded value" = Table.ExpandListColumn(#"Converted to Table", "value"),
#"Expanded value1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"objectId", "name", "description", "configuredBy", "users"}, {"value.objectId", "value.name", "value.description", "value.configuredBy", "value.users"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded value1",{{"@odata.context", type text}, {"value.objectId", type text}, {"value.name", type text}, {"value.description", type any}, {"value.configuredBy", type text}, {"value.users", type any}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"value.objectId", "value.name"}),
#"Invoked Custom Function" = Table.AddColumn(#"Removed Other Columns", "datasources", each GetDatasources([value.objectId]))
in
#"Invoked Custom Function"
Result
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
v-jingzhang many thanks for this. Please give me some time to test it out and I will update you by EO this week.
smpa01 That's ok. Besides, how do you generate the Bearer token? You can also use a custom function to get the token, then invoke this function in the GetDataSources function and the GetDataflow query to replace the static long token part in Headers.
"Besides, how do you generate the Bearer token?" -currently doing it manually from here https://docs.microsoft.com/en-us/rest/api/power-bi/dataflows/get-dataflows. Is there any other way to do that ? Would ❤ to know.
"You can also use a custom function to get the token" -Wow !!! how ? Would ❤ to know.
P.S. test still pending
Hi smpa01
This will need some additional steps. You could refer to below resources. It's worth trying because we don't need to generate access tokens manually and can avoid refresh failures caused by token expirations.
REST API Get Access Token - Microsoft Power BI Community
Building a Power BI Admin View [Part 2]: Obtaining a Refreshable Access Token - YouTube
Jing