How to pass parameter values to the url in Power query

[Krish89]

Hello,

I am trying to fetch the list of datasources used in all the datasets at tenant level using the below api endpoint.

https://api.powerbi.com/v1.0/myorg/admin/datasets/{datasetId}/datasources 

Below is the power query which fetches the datasources for only one dataset, but I would like to get the datasources for all the datasets in the tenant.

How can we dynamically pass parameter values to the url to get all the datasets datasources?

Regards,

Krishna.

[Krish89]

Hi @mahoneypat ,

I tried your solution but getting the below error.

Thanks,

Krishna

[mahoneypat]

Looks like appid is not a column, but a table.  You can add a custom column to a table that has a column called AppID and then use Json.Document(Web.Contents(..." & [AppID] & "...))

Regards,

Pat

[Krish89]

Hi @mahoneypat ,

I parse the column name called AppID but getting the below error.

Thanks,

Krishna

[mahoneypat]

You are seeing the error because you are trying to do this in your Source step instead of in an Table.AddColumn step.  Please see the below example.  It demonstrates an error on each line (the web call is not valid), but it shows the approach you should try.  The key is to have a column with your AppID values as text and then concatenate those value on each row into the web call.  You then expand the column of tables to combine the data for all the AppIDs.

let
Source = {2017..2021},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "YearID"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each Json.Document(Web.Contents("https://www.boxofficemojo.com/weekly/by-year/" & [YearID])))
in
#"Added Custom"

Pat

[mahoneypat]

You can make a table that has a column of all your dataset IDs and then add a custom column (or invoke a custom function) with your Source expression that concatenates that ID value as text into the larger expression

Web.Contents("... part of URL/" & [ColumnWithDatasetID] & "/rest of url...")

Pat