Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Russ
Helper I
Helper I

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

1 ACCEPTED SOLUTION

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hi @GilbertQ

 

That works very well. Many thanks.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.