Change the year of an URL dinamically

[mlsx4]

Hi!

I'm trying to connect to a calendar to get the bank holidays: https://www.calendarios-laborales.es/calendario-laboral-madrid-2023-m

Is there any way of change the year dinamically to current year?

Thank you in advance.

[danextian · Accepted solution]

Hi @mlsx4 ,

You can use DateTime.LocalNow() to get the NOW() equivalent of your local. Wrap it with Date.From, then with Date.Year and finally Text.From to convert the number to string as this will be concatenated with the rest of the URL. So the dynamic url would be

"https://www.calendarios-laborales.es/calendario-laboral-madrid-"  & Text.From( Date.Year( Date.From ( DateTime.LocalNow () ) ) ) & "-m" 

[mlsx4]

Thank you so much. Your solution works as expected. 

@danextian any way to keep the values for each year? I mean these are the holidays for 2023, but next year it will take only 2024. Is there a way to get 2023, 2024 and so on... together?

[danextian]

Hi @mlsx4 ,

Create a list of years. Assuming that you are to start from 2022 up to the current year, that would be

= {2022..Date.Year(Date.From(DateTime.LocalNow()))}

Convert this list to a table. Conver the column to text as this will be concatenated with the rest of the calendar URL. Create several custom columns to access the contents of the URLs. This is a sample query you can modify to suit your need

let
    Source = {2022..Date.Year(Date.From(DateTime.LocalNow()))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Year"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Year", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "URL", each "https://www.calendarios-laborales.es/calendario-laboral-madrid-" & [Year] & "-m", type text),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Web.BrowserContents", each Web.BrowserContents([URL]), type text),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Html.Table", each Html.Table([Web.BrowserContents], {{"Column1", ".col-sm-12 P"}, {"Column2", ".col-sm-12 SPAN:nth-child(4)"}, {"Column3", ".col-sm-12 .none + *"}, {"Column4", ".col-sm-12 .nacional"}}, [RowSelector=".col-sm-12 P"]), type table),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"URL", "Web.BrowserContents"}),
    #"Expanded Html.Table" = Table.ExpandTableColumn(#"Removed Columns", "Html.Table", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Html.Table","#(tab)","",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","#(lf)"," ",Replacer.ReplaceText,{"Column1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Replaced Value1",{{"Column1", "Date"}, {"Column2", "Description"}, {"Column3", "Location"}, {"Column4", "Day of Month"}}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Renamed Columns1", {{"Date", each Text.AfterDelimiter(_, "Madrid Madrid "), type text}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Extracted Text After Delimiter", {{"Date", each Text.BeforeDelimiter(_, " ", 3), type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Extracted Text Before Delimiter",{{"Date", Text.Trim, type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Trimmed Text",{{"Description", type text}, {"Location", type text}}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type1", "Day", each Text.BeforeDelimiter([Date], " "), type text),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Before Delimiter", "Month", each Text.AfterDelimiter([Date], "de "), type text),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Text After Delimiter",{"Date", "Day of Month"}),
    #"Added Custom3" = Table.AddColumn(#"Removed Columns1", "Date", each Text.Combine({[Day],[Month],[Year]}, "-")),
    #"Parsed Date" = Table.TransformColumns(#"Added Custom3",{{"Date", each Date.From(_, "es"), type date}})
in
    #"Parsed Date"

And this is the result.

[mlsx4]

You're amazing!! Thank you so much

[pratyashasamal]

Hi @mlsx4 ,
You can use DAX function to create Calender Table. The Calender table can also have recent dates.
You can follow this link :-
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Thanks,
Pratyasha Samal

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[mlsx4]

Hi @pratyashasamal 

Your answer has nothing to do with my question... I know how to use a calendar table perfectly, but my problem is in the URL of the website which includes a year that should be dynamic 

[Idrissshatila]

Hello @mlsx4 ,

What you can do is get all years and in power query filter to the current year, thus every year it will automatically shift.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

[mlsx4]

Hi @Idrissshatila 

Thanks for your answer, but the problem is that URL includes the year, therefore it would never reach another year