Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Krish89
Helper II
Helper II

How to pass parameter values to the url in Power query

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?

 

Krish89_0-1629897678916.png

 

Regards,

Krishna.

5 REPLIES 5
Krish89
Helper II
Helper II

Hi @mahoneypat ,

 

I tried your solution but getting the below error.

Krish89_1-1631613256820.png

 

Krish89_2-1631613334053.png

 

Thanks,

Krishna

 

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hi @mahoneypat ,

 

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

Krish89_1-1631624082887.png

 

Krish89_0-1631624057268.png

 

Thanks,

Krishna

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors