How to pass parameter values to the url in Power query

Question

Hello,&nbsp;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&nbsp;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?&nbsp;&nbsp;Regards,Krishna.

mahoneypat · Answer

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
&nbsp;
Web.Contents("... part of URL/" &amp; [ColumnWithDatasetID] &amp; "/rest of url...")
&nbsp;
Pat
&nbsp;

krish89 · Answer

Hi&nbsp;mahoneypat&nbsp;,&nbsp;I tried your solution but getting the below error.&nbsp;&nbsp;Thanks,Krishna&nbsp;

mahoneypat · Answer

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

krish89 · Answer

Hi&nbsp;mahoneypat&nbsp;,&nbsp;I parse the column name called AppID but getting the below error.&nbsp;&nbsp;Thanks,Krishna

mahoneypat · Answer

You are seeing the error because you are trying to do this in your Source step instead of in an Table.AddColumn step.&nbsp; Please see the below example.&nbsp; It demonstrates an error on each line (the web call is not valid), but it shows the approach you should try.&nbsp; 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.&nbsp; You then expand the column of tables to combine the data for all the AppIDs.
&nbsp;
letSource = {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/" &amp; [YearID])))in#"Added Custom"
&nbsp;
Pat
&nbsp;

Forum Discussion

How to pass parameter values to the url in Power query

5 Replies

Related content

TDS Endpoint multi value parameter PBRS

Power Query Parameter with default Value

Parameter single Value

Parameter: Selected Value doesn’t updated

power bi pass slicer value as query parameter