Dynamic URL for data refresh

[Anonymous]

Hello,

I am importing data from a public website https://covid19-static.cdn-apple.com/covid19-mobility-data-2020-07-01.csv

 However, the date changes daily to 2020-07-02, 03 and so on, I would want help to make it dynamic that whatever date is their in the URL path BI is able to pick that up and data can refresh accordingly. Right now, I am changing the source manually everyday.

Can someone help

Thanks,

[mahoneypat]

You can put an expression like this inside your Web.Contents( )

"https://covid19-static.cdn-apple.com/covid19-mobility-data-" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYY-MM-DD") &".csv"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hi @Pat

No it doesnt work shoes below error and I am creating this in blank query.  Pls help. 

[mahoneypat]

It looks like you are missing two right parentheses to close our your Csv.Document(Web.Content( functions.  Your Source line should look like this

= Csv.Document(Web.Contents("https://covid19-static.cdn-apple.com/covid19-mobility-data-" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYY-MM-DD") &".csv"))

However, I tried to get data from that URL and it appears to be down, so I can't confirm it.  

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

@Pat: Yes this query works but thr thing is it returns current date but date on the link is 2 days less than todays date always, so obviously the query wont work as its returning current date, can we tweak with the query and write a code so that it picks up whatever date is in yyyy-mm-dd format?

[camargos88 · Accepted solution]

Hi @Anonymous ,

Try scrapping this website, so you will get the updated link for it:

let
    Source = Web.BrowserContents("https://www.apple.com/covid19/mobility", [WaitFor = [Timeout = #duration(0,0,0,3)]]),
    Extract = Html.Table(
                            Source, 
                            {
                                {"Link", "#download-card > div.download-button-container > a", each [Attributes][href]}
                            },
                             [RowSelector="#download-card > div.download-button-container"]
                        ){0}[Link],
    GetData = Csv.Document(Web.Contents(Extract),[Delimiter=",",Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(GetData, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"geo_type", type text}, {"region", type text}, {"transportation_type", type text}, {"alternative_name", type text}, {"sub-region", type text}, {"country", type text}, {"2020-01-13", Int64.Type}, {"2020-01-14", Int64.Type}, {"2020-01-15", Int64.Type}, {"2020-01-16", Int64.Type}, {"2020-01-17", Int64.Type}, {"2020-01-18", Int64.Type}, {"2020-01-19", Int64.Type}, {"2020-01-20", Int64.Type}, {"2020-01-21", Int64.Type}, {"2020-01-22", Int64.Type}, {"2020-01-23", Int64.Type}, {"2020-01-24", Int64.Type}, {"2020-01-25", Int64.Type}, {"2020-01-26", Int64.Type}, {"2020-01-27", Int64.Type}, {"2020-01-28", Int64.Type}, {"2020-01-29", Int64.Type}, {"2020-01-30", Int64.Type}, {"2020-01-31", Int64.Type}, {"2020-02-01", Int64.Type}, {"2020-02-02", Int64.Type}, {"2020-02-03", Int64.Type}, {"2020-02-04", Int64.Type}, {"2020-02-05", Int64.Type}, {"2020-02-06", Int64.Type}, {"2020-02-07", Int64.Type}, {"2020-02-08", Int64.Type}, {"2020-02-09", Int64.Type}, {"2020-02-10", Int64.Type}, {"2020-02-11", Int64.Type}, {"2020-02-12", Int64.Type}, {"2020-02-13", Int64.Type}, {"2020-02-14", Int64.Type}, {"2020-02-15", Int64.Type}, {"2020-02-16", Int64.Type}, {"2020-02-17", Int64.Type}, {"2020-02-18", Int64.Type}, {"2020-02-19", Int64.Type}, {"2020-02-20", Int64.Type}, {"2020-02-21", Int64.Type}, {"2020-02-22", Int64.Type}, {"2020-02-23", Int64.Type}, {"2020-02-24", Int64.Type}, {"2020-02-25", Int64.Type}, {"2020-02-26", Int64.Type}, {"2020-02-27", Int64.Type}, {"2020-02-28", Int64.Type}, {"2020-02-29", Int64.Type}, {"2020-03-01", Int64.Type}, {"2020-03-02", Int64.Type}, {"2020-03-03", Int64.Type}, {"2020-03-04", Int64.Type}, {"2020-03-05", Int64.Type}, {"2020-03-06", Int64.Type}, {"2020-03-07", Int64.Type}, {"2020-03-08", Int64.Type}, {"2020-03-09", Int64.Type}, {"2020-03-10", Int64.Type}, {"2020-03-11", Int64.Type}, {"2020-03-12", Int64.Type}, {"2020-03-13", Int64.Type}, {"2020-03-14", Int64.Type}, {"2020-03-15", Int64.Type}, {"2020-03-16", Int64.Type}, {"2020-03-17", Int64.Type}, {"2020-03-18", Int64.Type}, {"2020-03-19", Int64.Type}, {"2020-03-20", Int64.Type}, {"2020-03-21", Int64.Type}, {"2020-03-22", Int64.Type}, {"2020-03-23", Int64.Type}, {"2020-03-24", Int64.Type}, {"2020-03-25", Int64.Type}, {"2020-03-26", Int64.Type}, {"2020-03-27", Int64.Type}, {"2020-03-28", Int64.Type}, {"2020-03-29", Int64.Type}, {"2020-03-30", Int64.Type}, {"2020-03-31", Int64.Type}, {"2020-04-01", Int64.Type}, {"2020-04-02", Int64.Type}, {"2020-04-03", Int64.Type}, {"2020-04-04", Int64.Type}, {"2020-04-05", Int64.Type}, {"2020-04-06", Int64.Type}, {"2020-04-07", Int64.Type}, {"2020-04-08", Int64.Type}, {"2020-04-09", Int64.Type}, {"2020-04-10", Int64.Type}, {"2020-04-11", Int64.Type}, {"2020-04-12", Int64.Type}, {"2020-04-13", Int64.Type}, {"2020-04-14", Int64.Type}, {"2020-04-15", Int64.Type}, {"2020-04-16", Int64.Type}, {"2020-04-17", Int64.Type}, {"2020-04-18", Int64.Type}, {"2020-04-19", Int64.Type}, {"2020-04-20", Int64.Type}, {"2020-04-21", Int64.Type}, {"2020-04-22", Int64.Type}, {"2020-04-23", Int64.Type}, {"2020-04-24", Int64.Type}, {"2020-04-25", Int64.Type}, {"2020-04-26", Int64.Type}, {"2020-04-27", Int64.Type}, {"2020-04-28", Int64.Type}, {"2020-04-29", Int64.Type}, {"2020-04-30", Int64.Type}, {"2020-05-01", Int64.Type}, {"2020-05-02", Int64.Type}, {"2020-05-03", Int64.Type}, {"2020-05-04", Int64.Type}, {"2020-05-05", Int64.Type}, {"2020-05-06", Int64.Type}, {"2020-05-07", Int64.Type}, {"2020-05-08", Int64.Type}, {"2020-05-09", Int64.Type}, {"2020-05-10", Int64.Type}, {"2020-05-11", type text}, {"2020-05-12", type text}, {"2020-05-13", Int64.Type}, {"2020-05-14", Int64.Type}, {"2020-05-15", Int64.Type}, {"2020-05-16", Int64.Type}, {"2020-05-17", Int64.Type}, {"2020-05-18", Int64.Type}, {"2020-05-19", Int64.Type}, {"2020-05-20", Int64.Type}, {"2020-05-21", Int64.Type}, {"2020-05-22", Int64.Type}, {"2020-05-23", Int64.Type}, {"2020-05-24", Int64.Type}, {"2020-05-25", Int64.Type}, {"2020-05-26", Int64.Type}, {"2020-05-27", Int64.Type}, {"2020-05-28", Int64.Type}, {"2020-05-29", Int64.Type}, {"2020-05-30", Int64.Type}, {"2020-05-31", Int64.Type}, {"2020-06-01", Int64.Type}, {"2020-06-02", Int64.Type}, {"2020-06-03", Int64.Type}, {"2020-06-04", Int64.Type}, {"2020-06-05", Int64.Type}, {"2020-06-06", Int64.Type}, {"2020-06-07", Int64.Type}, {"2020-06-08", Int64.Type}, {"2020-06-09", Int64.Type}, {"2020-06-10", Int64.Type}, {"2020-06-11", Int64.Type}, {"2020-06-12", Int64.Type}, {"2020-06-13", Int64.Type}, {"2020-06-14", Int64.Type}, {"2020-06-15", Int64.Type}, {"2020-06-16", Int64.Type}, {"2020-06-17", Int64.Type}, {"2020-06-18", Int64.Type}, {"2020-06-19", Int64.Type}, {"2020-06-20", Int64.Type}, {"2020-06-21", Int64.Type}, {"2020-06-22", Int64.Type}, {"2020-06-23", Int64.Type}, {"2020-06-24", Int64.Type}, {"2020-06-25", Int64.Type}, {"2020-06-26", Int64.Type}, {"2020-06-27", Int64.Type}, {"2020-06-28", Int64.Type}, {"2020-06-29", Int64.Type}, {"2020-06-30", Int64.Type}, {"2020-07-01", Int64.Type}, {"2020-07-02", Int64.Type}, {"2020-07-03", Int64.Type}, {"2020-07-04", Int64.Type}, {"2020-07-05", Int64.Type}, {"2020-07-06", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"geo_type", "region", "transportation_type", "alternative_name", "sub-region", "country"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"geo_type", type text}, {"region", type text}, {"transportation_type", type text}, {"alternative_name", type text}, {"sub-region", type text}, {"country", type text}, {"Date", type date}, {"Value", Int64.Type}})
in
    #"Changed Type1"

[Anonymous]

Yes this worked, amazing thank you so so much.. 🙂