- Microsoft Power BI Community
- Welcome to the Community!
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish Community
- Translated Spanish Desktop
- Power Platform Integration - Better Together!
- Power Platform Integrations
- Power Platform and Dynamics 365 Integrations
- Training and Consulting
- Instructor Led Training
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Engagement
- T-Shirt Design Challenge 2023
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

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

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Featured Topics

Top Solution Authors

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

104 | |

79 | |

71 | |

48 | |

47 |

Top Kudoed Authors

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

157 | |

89 | |

81 | |

69 | |

67 |