Running Rest API in Power query on each object

[smpa01]

Ref - https://docs.microsoft.com/en-us/rest/api/power-bi/

GET https://api.powerbi.com/v1.0/myorg/groups/{groupId}/dataflows 

If I use the above in web connector in a poewer query, gives me all the dataflow details from my workspace.

Now, I want to run 

GET https://api.powerbi.com/v1.0/myorg/groups/{groupId}/dataflows/{dataflowId}/datasources

for each of the df object returned in the last step, so that I can get the datasources for each of my dfs returned in a single table, in a single query.

This is what I tried so far, created a custom function called getDF_DS to run on each DF object

/*custom function called getDF_DS*/
(x as any) => let        
        Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/groups/xyz/dataflows/"&x&"/datasources", [Headers=[Authorization="Bearer reallyLong"]])),
        #"Converted to Table" = Table.FromRecords({Source}),
        #"Expanded value" = Table.ExpandListColumn(#"Converted to Table", "value"),
        #"Expanded value1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"datasourceType", "connectionDetails", "datasourceId", "gatewayId"}, {"value.datasourceType", "value.connectionDetails", "value.datasourceId", "value.gatewayId"}),
        #"Expanded value.connectionDetails" = Table.ExpandRecordColumn(#"Expanded value1", "value.connectionDetails", {"server", "database"}, {"value.connectionDetails.server", "value.connectionDetails.database"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Expanded value.connectionDetails",{{"@odata.context", type text}, {"value.datasourceType", type text}, {"value.connectionDetails.server", type text}, {"value.connectionDetails.database", type text}, {"value.datasourceId", type text}, {"value.gatewayId", type text}})
    in
        #"Changed Type"

let
    Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/groups/xyz/dataflows", [Headers=[Authorization="Bearer longStringHere"]])),
    #"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 text}, {"value.configuredBy", type text}, {"value.users", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"value.users"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([value.configuredBy] = "someuser@xyz.com")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each getDF_DS([value.objectId]))
in
    #"Added Custom"

It returns following

Expression.Error: Access to the resource is forbidden.

But let's suppose the Bearer "reallyLong" was generated for dfID=123 and I perform a filtered query like below it works, like below

let
    Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/groups/xyz/dataflows", [Headers=[Authorization="Bearer longStringHere"]])),
    #"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 text}, {"value.configuredBy", type text}, {"value.users", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"value.users"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([value.configuredBy] = "someuser@xyz.com")) and ([value.objectId] = "123")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each getDF_DS([value.objectId]))
in
    #"Added Custom"

Does it mean, that I need to generate Bearer value for each DFs and run seperate queries with seperate Berear values on corresponding Dfs? It defeats the purpose.  Is there a workaround

End Goal - I am trying to create a dependency map between dataflow and datasource. We have so many dataflows, lineage view is just not practical for us. Once I have the depency mapping I want to create a custom viz that would work for us in a network map/tree like structure.

Any help is greatly appreciated.

@ImkeF  @nickyvv 

[v-jingzhang · Accepted solution]

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.

[smpa01]

I don't know what was wrong with mine when I started. But I am glad tht you could spare some time to look into it and give me a solution. Thank you very much for your time !!!

[v-jingzhang]

I'm not sure either. I didn't find any error in your M codes. 

[smpa01]

@v-jingzhang  many thanks for this. Please give me some time to test it out and I will update you by EO this week.

[v-jingzhang]

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

[smpa01]

"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

[v-jingzhang]

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