Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Variable in URL

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Variable in URL

03-13-2017
12:52 PM

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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-13-2017
08:11 PM

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 4

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-13-2017
12:58 PM

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))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-13-2017
04:50 PM

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"

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-13-2017
08:11 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Announcements

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Featured Topics

Top Solution Authors

User | Count |
---|---|

209 | |

51 | |

43 | |

41 | |

40 |

Top Kudoed Authors

User | Count |
---|---|

269 | |

210 | |

72 | |

70 | |

65 |