topic Re: Getting data from Stats Canada API in Developer

Getting data from Stats Canada API

karansingla0311 — Wed, 15 Nov 2023 18:10:40 GMT

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:

https://www150.statcan.gc.ca/t1/tbl1/en/cv!recreate.action?pid=1410035401&selectedNodeIds=1D47,1D48,1D49,1D50&checkedLevels=1D1&refPeriods=20140101,20231201&dimensionLayouts=layout3,layout2,layout3&vectorDisplay=false

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?

Re: Getting data from Stats Canada API

lbendlin — Sun, 19 Nov 2023 01:41:01 GMT

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

Re: Getting data from Stats Canada API

karansingla0311 — Tue, 21 Nov 2023 20:24:59 GMT

Hi lbendlin,

This worked. Thank you so much!

Re: Getting data from Stats Canada API

mkjit256 — Fri, 21 Feb 2025 18:48:19 GMT

@lbendlin

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

Re: Getting data from Stats Canada API

lbendlin — Fri, 21 Feb 2025 22:00:05 GMT

You can modify your M code to accommodate the new URL

https://www150.statcan.gc.ca/t1/tbl1/en/dtl!downloadDbLoadingData-nonTraduit.action?pid=1410037901&latestN=0&startDate=20241201&endDate=20241201&csvLocale=en&selectedMembers=%5B%5B12%5D%2C%5B%5D%5D&checkedLevels=1D1%2C1D2%2C1D3