Getting data from Stats Canada API

[karansingla0311]

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]

Hi lbendlin,

This worked. Thank you so much!

[lbendlin]

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

[mkjit256]

@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

[lbendlin]

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&l...