Call Web API from Power Query / Dataflows

For a more in-depth tutorial: Call Web APIs like a pro in Power Query

Today, a lot of data is in SAAS solutions in the cloud. Like SalesForce, Exact Online and Dynamics 365. To use this data in Power BI you need to connect to the data via a Web API (Web Service). This can be challenging if you have never done this before. That’s why it’s important to learn how to Call Web API from Power Query effectively.

In this video I will show you step by step how this work and give you an introduction to Web API basics like: HTTP, JSON/XML data format + REST API. More information about the Power Query Formula Language: “M” Language Introduction.

General template: Connect tot REST API – XML Output

// Function definition 
// - input parameters: year as text
// - output datatype: table
( year as text ) as table =>

let  	//variable declarations:
	Source = 
	Xml.Tables( 
		Web.Contents(
            	//Base URL Web API:
	            "http://ergast.com/api/f1",
      	      [
	                //Sub path:
      	          RelativePath = year
            	]
		)  
	)          
in 	//return:
	Source