Forum Discussion

Russ's avatar
Russ
Helper I
9 years ago
Solved

Variable in URL

Is it possible to replace the day, month, year values in the following source URL with variables?

 

 

let
Source = Web.Page(Web.Contents("https://www.wunderground.com/weatherstation/WXDailyHistory.asp?ID=IGEORGEG2&day=3&month=3&year=2017&dayend=12&monthend=3&yearend=2017&graphspan=custom&format=1")),

 

 

For example, I would like to replace day=3 with Date.Day(Date.AddDays(DateTime.LocalNow(), -10))

 

Thanks,

Russ

Need Help
  • GilbertQ's avatar
    GilbertQ
    9 years ago

    hi Russ

     

    What you could do is in the Query Editor click on the Manage Parameters and create a new Parameter as with my Example it is called "DateDay"

     

    And then you would put this into your query below

     

    let
    Source = Web.Page(Web.Contents("https://www.wunderground.com/weatherstation/WXDailyHistory.asp?ID=IGEORGEG2&day= " & #"DateDay" & " &month= " & #"DateDay" & "&year=2017&dayend=12&monthend= " & #"DateDay" & " &yearend=2017&graphspan=custom&format=1")),
    Data0 = Source{0}[Data],
    #"Removed Columns" = Table.RemoveColumns(Data0,{"Kind", "Name", "Text"}),
    #"Expanded Children" = Table.ExpandTableColumn(#"Removed Columns", "Children", {"Children"}, {"Children.Children"}),
    #"Expanded Children.Children" = Table.ExpandTableColumn(#"Expanded Children", "Children.Children", {"Text"}, {"Children.Children.Text"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Children.Children", each ([Children.Children.Text] <> null)),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows","Children.Children.Text",Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),{"Children.Children.Text.1", "Children.Children.Text.2", "Children.Children.Text.3", "Children.Children.Text.4", "Children.Children.Text.5", "Children.Children.Text.6", "Children.Children.Text.7", "Children.Children.Text.8", "Children.Children.Text.9", "Children.Children.Text.10", "Children.Children.Text.11", "Children.Children.Text.12", "Children.Children.Text.13", "Children.Children.Text.14", "Children.Children.Text.15", "Children.Children.Text.16"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Children.Children.Text.1", type text}, {"Children.Children.Text.2", type text}, {"Children.Children.Text.3", type text}, {"Children.Children.Text.4", type text}, {"Children.Children.Text.5", type text}, {"Children.Children.Text.6", type text}, {"Children.Children.Text.7", type text}, {"Children.Children.Text.8", type text}, {"Children.Children.Text.9", type text}, {"Children.Children.Text.10", type text}, {"Children.Children.Text.11", type text}, {"Children.Children.Text.12", type text}, {"Children.Children.Text.13", type text}, {"Children.Children.Text.14", type text}, {"Children.Children.Text.15", type text}, {"Children.Children.Text.16", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Children.Children.Text.1", "Children.Children.Text.3"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Other Columns")
in
    #"Promoted Headers"

    If you look at the code you will see that I have replaced the 3 with the following: " & #"DateDay" & " 

    And this is the name of the parameter I defined earlier.

    I tried this syntax in my Query Editor and it works as expected.

     

    I hope that it helps

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Russ

     

    To replace day = 3 you can use suggested string ;-)

     

     

    Source = Web.Page(Web.Contents("https://www.wunderground.com/weatherstation/WXDailyHistory.asp?ID=IGEORGEG2&
    Date.Day(Date.AddDays(DateTime.LocalNow(), -10)
    &month=3&year=2017&dayend=12&monthend=3&yearend=2017&graphspan=custom&format=1")),d=2017&graphspan=custom&format=1")),

     

    you may even go deeper and create a parameter:

     

    Source = Web.Page(Web.Contents("https://www.wunderground.com/weatherstation/WXDailyHistory.asp?ID=IGEORGEG2&
DAY & 
&month=3&year=2017&dayend=12&monthend=3&yearend=2017&graphspan=custom&format=1")),

     

    and the parameter for DAY is:

    Date.Day(Date.AddDays(DateTime.LocalNow(), -10))

     

     

    • Russ's avatar
      Russ
      Helper I
      9 years ago

      HI Anonymous

       

      Thanks for your reply. 

       

      Apologies as I was not totally clear, but I was only looking to replace the 3 with a variable (not day =3). As per your suggestion above, if I simply replace the 3 with the code 

       

      Date.Day(Date.AddDays(DateTime.LocalNow(), -10))

      the query does not work as intended.

       

      To see the issue my original query is below, returning the average temperature from a weather station for the previous 10 days.

       

      let
    Source = Web.Page(Web.Contents("https://www.wunderground.com/weatherstation/WXDailyHistory.asp?ID=IGEORGEG2&day=3&month=3&year=2017&dayend=12&monthend=3&yearend=2017&graphspan=custom&format=1")),
    Data0 = Source{0}[Data],
    #"Removed Columns" = Table.RemoveColumns(Data0,{"Kind", "Name", "Text"}),
    #"Expanded Children" = Table.ExpandTableColumn(#"Removed Columns", "Children", {"Children"}, {"Children.Children"}),
    #"Expanded Children.Children" = Table.ExpandTableColumn(#"Expanded Children", "Children.Children", {"Text"}, {"Children.Children.Text"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Children.Children", each ([Children.Children.Text] <> null)),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows","Children.Children.Text",Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),{"Children.Children.Text.1", "Children.Children.Text.2", "Children.Children.Text.3", "Children.Children.Text.4", "Children.Children.Text.5", "Children.Children.Text.6", "Children.Children.Text.7", "Children.Children.Text.8", "Children.Children.Text.9", "Children.Children.Text.10", "Children.Children.Text.11", "Children.Children.Text.12", "Children.Children.Text.13", "Children.Children.Text.14", "Children.Children.Text.15", "Children.Children.Text.16"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Children.Children.Text.1", type text}, {"Children.Children.Text.2", type text}, {"Children.Children.Text.3", type text}, {"Children.Children.Text.4", type text}, {"Children.Children.Text.5", type text}, {"Children.Children.Text.6", type text}, {"Children.Children.Text.7", type text}, {"Children.Children.Text.8", type text}, {"Children.Children.Text.9", type text}, {"Children.Children.Text.10", type text}, {"Children.Children.Text.11", type text}, {"Children.Children.Text.12", type text}, {"Children.Children.Text.13", type text}, {"Children.Children.Text.14", type text}, {"Children.Children.Text.15", type text}, {"Children.Children.Text.16", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Children.Children.Text.1", "Children.Children.Text.3"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Other Columns")
in
    #"Promoted Headers"

       

       

      • GilbertQ's avatar
        GilbertQ
        Super User
        9 years ago

        hi Russ

         

        What you could do is in the Query Editor click on the Manage Parameters and create a new Parameter as with my Example it is called "DateDay"

         

        And then you would put this into your query below

         

        let
    Source = Web.Page(Web.Contents("https://www.wunderground.com/weatherstation/WXDailyHistory.asp?ID=IGEORGEG2&day= " & #"DateDay" & " &month= " & #"DateDay" & "&year=2017&dayend=12&monthend= " & #"DateDay" & " &yearend=2017&graphspan=custom&format=1")),
    Data0 = Source{0}[Data],
    #"Removed Columns" = Table.RemoveColumns(Data0,{"Kind", "Name", "Text"}),
    #"Expanded Children" = Table.ExpandTableColumn(#"Removed Columns", "Children", {"Children"}, {"Children.Children"}),
    #"Expanded Children.Children" = Table.ExpandTableColumn(#"Expanded Children", "Children.Children", {"Text"}, {"Children.Children.Text"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Children.Children", each ([Children.Children.Text] <> null)),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows","Children.Children.Text",Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),{"Children.Children.Text.1", "Children.Children.Text.2", "Children.Children.Text.3", "Children.Children.Text.4", "Children.Children.Text.5", "Children.Children.Text.6", "Children.Children.Text.7", "Children.Children.Text.8", "Children.Children.Text.9", "Children.Children.Text.10", "Children.Children.Text.11", "Children.Children.Text.12", "Children.Children.Text.13", "Children.Children.Text.14", "Children.Children.Text.15", "Children.Children.Text.16"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Children.Children.Text.1", type text}, {"Children.Children.Text.2", type text}, {"Children.Children.Text.3", type text}, {"Children.Children.Text.4", type text}, {"Children.Children.Text.5", type text}, {"Children.Children.Text.6", type text}, {"Children.Children.Text.7", type text}, {"Children.Children.Text.8", type text}, {"Children.Children.Text.9", type text}, {"Children.Children.Text.10", type text}, {"Children.Children.Text.11", type text}, {"Children.Children.Text.12", type text}, {"Children.Children.Text.13", type text}, {"Children.Children.Text.14", type text}, {"Children.Children.Text.15", type text}, {"Children.Children.Text.16", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Children.Children.Text.1", "Children.Children.Text.3"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Other Columns")
in
    #"Promoted Headers"

        If you look at the code you will see that I have replaced the 3 with the following: " & #"DateDay" & " 

        And this is the name of the parameter I defined earlier.

        I tried this syntax in my Query Editor and it works as expected.

         

        I hope that it helps