Forum Discussion
Dynamic Date and element in Power BI URL
Mehay ,
Yes, it's like a macro in Excel.
It keeps all the steps and apply them when the refresh happens.
Hi Mehay ,
We get this error if the page load is slower than the Power Query request, to fix it, use this code:
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]) in GetDataI just added a timer to delay the load of the page in Power Query.
Hi camargos88,
You mean right here ? DId i get it right?
And that code will mean, it will take data aoutomatically as the latest one whenever i click on refresh right? So basically i wont need to type dates in Invoke Function anymore?
Modified fuction as per you prev message.
let
Source = #"Apple Mobility Trend"(Date.From(DateTime.LocalNow())),
Thanks in advance,
Mehdi
Mehay ,
First create the function, I named it as fn_FindValidURL:
(dt as date) as table =>
let
Url =
Web.Contents(
"https://covid19-static.cdn-apple.com/covid19-mobility-data/2009HotfixDev28/v3/en-us/applemobilitytrends-"&Date.ToText(dt, "yyyy-MM-dd")&".csv"
,[ManualStatusHandling={404}]
),
Result =
if Value.Metadata(Url)[Response.Status] = 404 then
@fn_FindValidURL(Date.AddDays(dt, -1))
else Csv.Document(Url,[Delimiter=",",Encoding=65001, QuoteStyle=QuoteStyle.None])
in
Result
And use this mcode to call the function with the current date:
let
Source = fn_FindValidURL(Date.From(DateTime.LocalNow())),
#"Promoted Headers" = Table.PromoteHeaders(Source, [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}}),
#"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"
This function will start with the current date and try a valid url, it will stop when the first url is found.
The part:
if Value.Metadata(Url)[Response.Status] = 404 then
@fn_FindValidURL(Date.AddDays(dt, -1))
else Csv.Document(Url,[Delimiter=",",Encoding=65001, QuoteStyle=QuoteStyle.None])
Checks if the url is valid, if you get 404 as return, try again decreasing 1 day...and so on. If the error is <> than 404, read the url.
This link is updated with the date, and the previous files are not available anymore, correct ?
"and also this part in URL changes too: "2009HotfixDev28/" now for 10 Jun data it shows to be 14 instead of 28."
If I got the correct current link, it's:
https://covid19-static.cdn-apple.com/covid19-mobility-data/2010HotfixDev14/v3/en-us/applemobilitytrends-2020-06-10.csv
It's possible to create a recursive function for it, however we are talking about 3 variables....it's problem to call the link so many times.