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 Mehay ,
You just need to create a table with 1 valiue (current date using Date.From(DateTime.LocalNow()), use the function and expand the table.
This recursive function will decrease the date and try to find a valid url.
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
and also this part in URL changes too: "2009HotfixDev28/" now for 10 Jun data it shows to be 14 instead of 28.
Thanks,
Mehdi
Mehay ,
We can try another approach for this, scrap this page and get the link directly:
let Source = Web.BrowserContents("https://www.apple.com/covid19/mobility"), 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 GetDataDear camargos88,
Appreciate for you help, first of all.
Yes, your function basically worked fine, i just copied all codes given by you.
The thing is if I dont change 28 in URL to 14, it would still stick to data untill 9 Jun.
So only when i changed it to 14, it gave me needed data untill 10 Jun.
I checked with old URL, and it's probably still saved within Apple database with the same old link but wont be avialable publically in the website.
The website would provide only lateast data.
So I think that would be another problem with this formula.
Just my thought, is it possible to have codes with that stick strictly to d-2, so 2 days before, e.i. today 12 Jun, so to get 10 Jun), and then for that middle number in the URL it will count from 0-100, and therefore, the system will try to figure out wich number should match with 10 Jun data.
Thanks again.
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 ResultAnd 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.