Forum Discussion

karansingla0311's avatar
karansingla0311
Regular Visitor
2 years ago

Getting data from Stats Canada API

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 won...
Need Help
lbendlin's avatar
lbendlin
Super User
2 years ago

 

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