Re: Getting data from Stats Canada API

karansingla0311 · ‎11-15-2023

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,...

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?

karansingla0311 · ‎11-21-2023

Hi lbendlin,

This worked. Thank you so much!

lbendlin · ‎11-18-2023

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