Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Good evening!
Thanks in advance for your help! I have a question, I want to import the data from Statistics Canada, I thought I would be able to pull the tables via "Web Data Service" but I was wondering if you recomend other ways to import the data tables and if this tables will be updated automatically as the site updates their numbers? An example of the table I want to pull is this one:
I have tried to use Web API Connector for the download links provided in "Download Options" but it was not giving me the data as per the above link.
Is there a way I can get this data into powerbi without needed to manually updating file?
Hi lbendlin,
This worked. Thank you so much!
let
Source = Csv.Document(Web.Contents("https://www150.statcan.gc.ca"
,[RelativePath = "t1/tbl1/en/dtl!downloadDbLoadingData-nonTraduit.action?pid=1410035401&latestN=1&startDate=&endDate=&csvLocale=en&selectedMembers=%5B%5B%5D%2C%5B109%5D%5D&checkedLevels=0D1"
])
,[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"REF_DATE", type date}, {"GEO", type text}, {"DGUID", type text}, {"Effective date", type text}, {"UOM", type text}, {"UOM_ID", Int64.Type}, {"SCALAR_FACTOR", type text}, {"SCALAR_ID", Int64.Type}, {"VECTOR", type text}, {"COORDINATE", type number}, {"VALUE", type number}, {"STATUS", type text}, {"SYMBOL", type text}, {"TERMINATED", type text}, {"DECIMALS", Int64.Type}})
in
#"Changed Type"
For some reason I can't get the Query part to work, but the RelativePath should already give you the required flexibility
The solution you have provided doesn't work for this url:
Employment by industry, three-month moving average, unadjusted for seasonality, inactive
Help appreciated.
Thanks
You can modify your M code to accommodate the new URL
User | Count |
---|---|
5 | |
4 | |
4 | |
2 | |
2 |
User | Count |
---|---|
8 | |
4 | |
4 | |
4 | |
3 |