Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
karansingla0311
Regular Visitor

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 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? 

4 REPLIES 4
karansingla0311
Regular Visitor

Hi lbendlin,

This worked. Thank you so much!

lbendlin
Super User
Super User

 

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

 

@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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.