Business Intelligence, Data Warehouse and Data Lake

SQTH08 Call Web APIs Like a Pro in Power Query


2:30pm - 3:45pm

Level: Intermediate

Arthur Graus

Power BI Trainer / Consultant

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.

In this session, I will show you step by step what to do and what pitfalls to avoid. The following topics will be covered:

  • Introduction to Web APIs: JSON/XML data format, REST API
  • Paging: often you can't get all the data at once and you have to call the Web API several times, page by page.
  • Custom Functions in M: the building blocks of every Web API call
  • Incremental Refresh: since fetching all data can be very slow via a Web API, it is better to only load new data.
  • Authentication using API Token or OAuth (Access Token)

You will learn:

  • Call REST API from Power Query / Power BI
  • Implement paging
  • Authenticate using API Token or OAuth