Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to 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"
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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?
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"
Yes this worked, amazing thank you so so much.. 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
50 | |
19 | |
12 | |
11 |