Forum Discussion
Are nested parameters supported ?
I am using a template which i used to create reports for multuple customers.
I use one parameter for customer name.
Based on this customer name ( which i will chose when opening PBIT template) i want Powerbi to look up for a URL ( which is unique to each customer) and then issue a query to that url and pull data.
I can maintain this URL in a csv, or in a custom table within PBIX, but i cant get this working. Is this because nested parameters are not supported.
i tried two ways
1. Created a URL list in a csv and saved locally in side a customer name folder. When i open PBIX i chose customer and this customer name is used as a filter in query to pick only that customers URL and this url column is coverted to a list and this list is configured on the query parameter as URL. Then i used this URL parameter in teh actual query to pull data.
When i open PBIT, it wont populate URL field even after picking customer name.
2. Create a custom table inside PBIX with customer and URL mapping. Similar to above, i configured a filter in the query based on customername parameter, and converted that URL column to a list and list query added into a URL parameter.
same result.
appreciate any help, already spent lot of time on this....
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.
4 Replies
Hi Anonymous
If the customer name is a nested parameter in the url, then you can create a parameter for customer name, then use the parameter in the url.
If not, please let me know if all urls have different structures or are compeleted different.
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.