4 followers Follow

Power Query and Rest API

Power Query is self-service business intelligence (BI) for Excel. It allow for discovering, combining, and refining data across a wide variety of sources including relational, structured and semi-structured data set including JSON. 

There are lots of resource on the web and few good books to learn about Power Query and the M-Language so we will not try to provide a tutorial on how to use Power Query but rather encourage you to experiment on your own to get the basic understanding of technology. But if really asked me how to get started, my personal favorite is the book by  Ken Puls and Miguel Escobar: "M Is for (Data) Monkey: A Guide to the M Language in Excel Power Query"

Regardless of how you learn about power query, setting up Excel to consume CAST Rest API on your own, by trial and error, may be a bit time consuming. You might also quickly discover that there are many way accomplish the same results although some are harder to maintain.

To explain how we did it (and what we recommend you do), we decided to offer you few examples as these are often the best and quickest way to learn about something. So here is an Excel 2016 workbook with built in Power Query that extract a variety of data set. Use it as is to connect to your Rest API, modify and/or add new queries. And don't forget to have fun with your data visualization!

P.S. Credential for the Rest API are not embedded for security reason in the workbook itself and must be set as part of the connection details. This is a feature of Power Query. Here's how you set your connection to your Rest API:

  1. In the CONFIG tab enter the URL of your rest API
  2. Select New Query > Open Connection Properties
  3. Select your API connection in the Global Permission list and click on Edit Permission
  4. In the Edit Permissions windows select Edit in the Credential section. Set the credential appropriate for your Rest API. 


Please sign in to leave a comment.



Hello all, Power BI is one of the most powerful business analytic tool provided by Microsoft and its innumerable uses prove that it is here to stay. I myself a techie like to explore about business analytic tools and so here is another blog which describes an useful aspect of Power BI. I have no intention of questioning the blogger's knowledge. I just want to share some knowledge:

0 votes
Comment actions Permalink