Forum Discussion
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/WXDailyHistor...
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
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))
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"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
