Query Parameter Testing

[bobrin]

I am testing query parameters.  I have a Web URL that is constructed of 4 parameters.  Two of the parameters contain multiple values that I want to iterate over using the same base URL parameters.

Any thoughts on how I would iterate through the values of the 'Season' and the 'Page' parameters and create a table that was appended for all values where the parameters changed?

Here is the script:

let
    Source = Web.Page(Web.Contents(BaseURL & Season & MidURL & Page & EndURL)),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"RK", Int64.Type}, {"Player", type text}, {"Team", type text}, {"Pos", type text}, {"G▼", Int64.Type}, {"AB▼", Int64.Type}, {"R▼", Int64.Type}, {"H▼", Int64.Type}, {"2B▼", Int64.Type}, {"3B▼", Int64.Type}, {"HR▼", Int64.Type}, {"RBI▼", Int64.Type}, {"BB▼", Int64.Type}, {"SO▼", Int64.Type}, {"SB▼", Int64.Type}, {"CS▼", Int64.Type}, {"AVG▼", type number}, {"OBP▼", type number}, {"SLG▼", type number}, {"OPS▼", type number}, {"IBB▼", Int64.Type}, {"HBP▼", Int64.Type}, {"SAC▼", Int64.Type}, {"SF▼", Int64.Type}, {"TB▼", Int64.Type}, {"XBH▼", Int64.Type}, {"GDP▼", Int64.Type}, {"GO▼", Int64.Type}, {"AO▼", Int64.Type}, {"GO_AO▼", type number}, {"NP▼", Int64.Type}, {"PA▼", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Season", each Season)
in
    #"Added Custom"

[arify]

Web.Contents has a special functionality. Here's a nice example: http://www.excel2013.info/power-query/connection-api/

You'll need to use Query option: (example in that page)

Web.Contents(“http://maps.googleapis.com/maps/api/distancematrix/xml”,
[Query=[origins =”Paris”, destinations =”Amsterdam”, sensor =”false”, mode=”driving”, units = “metric”]])

[ImkeF]

try sth like this:

let
   
   // These are your parameters or queries you already have - in this case just provided for demonstration purposes
   BaseURL = "StartWithBaseURL",
   MidURL = "SomeMidURLHere",
   EndURL = "EndWithBaseURL",
   Season = #table(type table[Season=text],{{"Spring"},{"Summer"}}),
   Page = #table(type table[Page=text],{{"1"},{"2"},{"3"}}),
  
   // Start of actual query
    Source = Table.AddColumn(Season, "Page", each Page),
    #"Expanded Page" = Table.ExpandTableColumn(Source, "Page", {"Page"}, {"Page"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Page", "Custom", each Web.Page(Web.Contents(BaseURL & [Season] & MidURL & [Page] & EndURL))),
    Custom1 = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    Custom1

So not much has change in comparison to your start: BaseURL & [Season] & MidURL & [Page] & EndURL

Just some brackets: [...]

But they make the difference, as they refer to columns from a table that you need to build (if the values for "Season" and "Page" sit in 2 independet lists) or refer to (combinations of Season & pages). So you build or start from the table that contains the desired combinations of Season and Page and then add a column that performs the web-calls. The last expand-step (Custom1) might need to be adjusted. This is best done manually.

[bobrin]

Thank you Imke.  This certainly works but does not really take advantage of the fact that the Season and Pages were defined using the Query Parameters interface.  I was trying to understand how I could take advantage of that dialog in the M script.

Also - not related to the solution per se.  My reference to Season was more of a reference to the year not a the time of year.  The 2014 Season or the 2015 Season vs. Spring, Winter, etc...  (Sports reference).

[ImkeF]

Not quite sure what you want to accomplish here: Do want to know how to set up the parameters so that the user can select a value from a predefined list? Check out this post: https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/

an here: https://blog.crossjoin.co.uk/2016/05/15/creating-m-functions-from-parameterised-queries-in-power-bi/

Or try to define your specific request more precisely.