Forum Discussion
Are nested parameters supported ?
Hi Anonymous
I create a "Map table", a parameter "customer_name", a new query to show the data based on different customer name.
When open the pbix file, enter a customer name like "a", then it would show data from the url which belongs to customer "a".
Map table
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vY0xDsMgDAD/4jmJow4dKkUdUIe+gTAQQlOkgBE2Ivl984pud7rhtAYLHXxFMj8QW2tDDK4Q00cGRxF96uOJLFQ8CuU+27DiZqNnPBY6nqpeLfryTuKTTIoS10vnOo63u7PiNyrn9FrrxYGS3cF0GpY/PNVumYNjMOYH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"customer name" = _t, url = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"customer name", type text}, {"url", type text}}) in #"Changed Type"Parameter: customer_name
"a" meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]New query
let url1 = Table.SelectRows(#"Map table", each ([customer name] = customer_name))[url]{0}, Source = Web.BrowserContents(url1), #"Extracted Table From Html" = Html.Table(Source, {{"Column1", ".c-subheading-6"}, {"Column2", "[itemprop=""ratingValue""]"}, {"Column3", "P + .x-screen-reader"}, {"Column4", "[itemprop=""reviewCount""]"}, {"Column5", "[itemprop=""priceCurrency""]"}, {"Column6", "[itemprop=""bestRating""]"}, {"Column7", ".c-rating:nth-child(2) .x-screen-reader:nth-child(1)"}, {"Column8", "S"}, {"Column9", "SPAN + .x-screen-reader"}, {"Column10", "SPAN + .x-screen-reader + *"}}, [RowSelector=".m-channel-placement-item"]), #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type number}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", Int64.Type}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}}) in #"Changed Type"The "#"Extracted Table From Html" part depends on the web service.
You can firstly connect to the web, then get this part, next paste the "#"Extracted Table From Html" part in the new query.
This is limited for only urls which is from the same web service and have the same "#"Extracted Table From Html" part.
If this parts are different for many urls, please define it manually in the new query.
Please download my file to see details.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
yea they are all difffrent, just to rephrase
1. Parameter 1 -Customer name - contosso.com-
2. Parameter 2- Customer URL - https://xyz.wefe3434. ( this url wont have a customer name, its completely unique url per customer)
When i open the template, i have a query that uses customer url to pull some data from the URL.
My requirement is, when i open template, i chose customer name from the drop down ( parameter), i want PBI to pick the url from a mapping table and issue a query to the URL. When i pick second customer ,it has to pick corresponding URL and issue the query to a diffrent URL
Hi Anonymous
I create a "Map table", a parameter "customer_name", a new query to show the data based on different customer name.
When open the pbix file, enter a customer name like "a", then it would show data from the url which belongs to customer "a".
Map table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vY0xDsMgDAD/4jmJow4dKkUdUIe+gTAQQlOkgBE2Ivl984pud7rhtAYLHXxFMj8QW2tDDK4Q00cGRxF96uOJLFQ8CuU+27DiZqNnPBY6nqpeLfryTuKTTIoS10vnOo63u7PiNyrn9FrrxYGS3cF0GpY/PNVumYNjMOYH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"customer name" = _t, url = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"customer name", type text}, {"url", type text}})
in
#"Changed Type"Parameter: customer_name
"a" meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]New query
let
url1 = Table.SelectRows(#"Map table", each ([customer name] = customer_name))[url]{0},
Source = Web.BrowserContents(url1),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", ".c-subheading-6"}, {"Column2", "[itemprop=""ratingValue""]"}, {"Column3", "P + .x-screen-reader"}, {"Column4", "[itemprop=""reviewCount""]"}, {"Column5", "[itemprop=""priceCurrency""]"}, {"Column6", "[itemprop=""bestRating""]"}, {"Column7", ".c-rating:nth-child(2) .x-screen-reader:nth-child(1)"}, {"Column8", "S"}, {"Column9", "SPAN + .x-screen-reader"}, {"Column10", "SPAN + .x-screen-reader + *"}}, [RowSelector=".m-channel-placement-item"]),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type number}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", Int64.Type}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}})
in
#"Changed Type"The "#"Extracted Table From Html" part depends on the web service.
You can firstly connect to the web, then get this part, next paste the "#"Extracted Table From Html" part in the new query.
This is limited for only urls which is from the same web service and have the same "#"Extracted Table From Html" part.
If this parts are different for many urls, please define it manually in the new query.
Please download my file to see details.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.