Read API Data with Power BI using Power Query


By:   |   Updated: 2020-02-12   |   Comments (2)   |   Related: More > Power BI


Problem

There are a lot of great APIs that can provide data input for reports.  More people are using Power BI to develop reports and it would be great if we could use an API to load data directly into Power BI for reporting on the data.  In this tip, we will walk through how this can be done. 

Solution

This tip will help you get started with API data using Power Query M Formula language. The examples will cover both graphical and line code programming using Power Query Editor as well as give a short introduction to the syntax of Power Query M formula language.

Power Query M formula language

The Power Query M formula language is now embedded into Excel, SSAS and Power BI. The M stands for Mash-Up and its primary role is data manipulation. According to Microsoft, the syntax is case sensitive and the language is functionally like F#.

Here is a simple code example to get familiar with data Mash-Up:

let //use let to declare variables
TextVariable = Text.Proper("hello world") 

in //use in to call variables defined in let
TextVariable
  
//this code block will return a single data string "hello world" 

The expression "let" is used to assign variable names and values, where the "in" statement uses the variables defined in "let". (More on Mash-Up syntax can be found here)

How to use M language in Power BI

Start Power BI and select "Get Data" from a blank query as shown below:

power bi get data
Figure 1: Get data from Blank Query

Choose Blank Query as shown in the picture above and click "Connect". This will open the Power Query Editor:

power query editor
Figure 2: Power Query Editor Query1

Let's embed our small M script into the Power BI Query. Right click on Query1 and then select "Advanced Editor" and copy the code.

power query advanced editor
Figure 3: Advanced Editor for M script "hello world"

Click "Done" to close and apply the changes to the Advanced Editor. Query1 will return the text string "Hello World".

power query editor
Figure 4: Hello World preview in Power Query Editor

Click "Close & Apply" to save the changes and Power BI will now fetch the data:

power query evaluating query
Figure 5: Power BI data refresh

For the purposes of this tip, I am using a simple card visualization to illustrate the result and we can see below the M language query result presented in a Power BI report.

power query results in power bi
Figure 6: Power BI Report card for "Hello World"

M language Example: GET request followed by JSON parsing to the table

Before we go through API examples with Power Query M formula language, it's worth mentioning that Power BI has a standard Web data source that can be used without any coding at all, as described by an earlier tip. Unfortunately, the standard Web data source has limitations, when it comes to passing parameters and combining several GET requests into one query.

For the purpose of this tip I will be using the free API from https://docs.openaq.org/. As with all APIs the documentation is very important as it is the basis for any good data consumer project.

Let's start simple by querying the list of all countries that are participating in the clean air initiative.

In the Power Query Editor Create a new Blank Query called "Countries" and then add the following code to it:

let 
   Source = Json.Document(Web.Contents("https://api.openaq.org/v1/countries")) 
   //WebContents by default executes a GET request 
   //Json.Document processes text for web into a JSON output 

in
   Source
  
//this code block will return a single data string "hello world" 

The Advanced editor should look like this:

power query advanced editor
Figure 7: Advanced Query Editor API Countries

Check for any syntax errors and click "Done". You will be presented with the following result:

power query meta data
Figure 8: Power Query Editor Data preview countries

In red, I marked the two results that one gets from an API GET request in Power BI. One is called Meta and it contains the metadata returned from the API provider. Click on "Record" to see the metadata that has been actually returned in the call:

power query meta data
Figure 9: Countries GET request metadata

Apart from the name, license and website, we have information like page number, limit (this is the default limit) and the number of records (92). This is not something we will use in our reports, but we will be working with metadata later.

To go back, delete the Metadata navigation step as marked in red in lower right corner as shown below:

power query meta data
Figure 10: Remove the metadata step

Now click on the List of results to see what data has been returned by the API GET request:

power query data
Figure 11: Expand the list of results

This is the list of 92 records each of which consists of one country as shown below:

power query data
Figure 12: View of one country record

To convert the list of Records to a Country table click the "To Table" button as marked below:

power query list tools
Figure 13: Convert list of records to table

Click OK on the dialog box below:


Figure 14: Handling error in conversion

Our dataset does not use delimiters, so this is the result of List to table conversion:

power query editor
Figure 15: Split the column by its underlying attributes

Now we have one column that describes all attributes of a country. This is not something one can use in a report. We need to split the attributes into individual columns.

Click on the button marked in red to the right of Column1 as shown in the picture above.  Now all country attributes are in separate columns and can easily be used in a Power BI report. Click on "Close and Apply" to use the data.

power query data
Figure 16: Final result of countries table

As shown below, the code returned is an ISO standard 2-character code associated with each country in a Power BI filled map object by Bing.

power bi map
Figure 17: Countries map using ISO code

Wait, what has actually happened to our Power Query M formula language? We have been using the GUI for most of our steps. Well the GUI was programming the steps in the Power Query M formula language. Go back to the Power Query Editor and select Advanced Editor and you will find the following code:

power query advanced editor
Figure 18: M-Script for countries JSON API to table
let 
   Source = Json.Document(Web.Contents("https://api.openaq.org/v1/countries")) 
   results = Source[results], 
   #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
   #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"code", "count", "locations", "cities", "name"}, {"Column1.code", "Column1.count", "Column1.locations", "Column1.cities", "Column1.name"})
in
   #"Expanded Column1"

Each line in the let block corresponds to each step added to our query. See the steps shown below:

power query settings
Figure 19: M-Script steps

M language Example: GET request with Parameters

Power Query M formula language is very handy when you need to pass parameters with GET requests. Here the standard web source unfortunately lacks functionality.

For this example, we will query a list of cities from https://api.openaq.org/v1/cities. If we run the same M script as before without passing any parameters the request will return the following metadata results:

power query meta data
Figure 20: Metadata for cities GET request

The metadata tells us that the system has found 2837 records, but the request has a default limit of 100. That means we only got 100 records out of 2837. We will need to change the limit. Let's do that as shown below:

let   
   cities_url = "https://api.openaq.org/v1/cities", //Web Url
   request_limit = "10000", //number represented by a string
   Source = Json.Document(Web.Contents(cities_url, [Query = [limit = request_limit]])) //Query is used to pass parameters
in
   Source 

Now let's have a look at the metadata once more:

power query meta data
Figure 21: Metadata for cities GET request with maximum limit

The limit has changed from 100 to 10000 and it is key that it is more than the number of records.

If we go through the steps of data transformations as we did above, we should get a city table that looks like this:

power query data
Figure 22: Cities GET request data after being transformed from JSON to table

The M script code for the query and transformation is shown below:

let     
    cities_url = "https://api.openaq.org/v1/cities", //Web Url 
    request_limit = "10000", //number represented by a string 
    Source = Json.Document(Web.Contents(cities_url, [Query = [limit = request_limit]])), 
    // number represented by a string 
    results = Source[results], 
    #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Cities"}}), 
    #"Expanded Cities" = Table.ExpandRecordColumn(#"Renamed Columns", "Cities", {"country", "name", "city", "count", "locations"}, {"Cities.country", "Cities.name", "Cities.city", "Cities.count", "Cities.locations"}) 
in 
    #"Expanded Cities"

M language Example: Combining GET requests

Let's run another example where the first GET request will return the number of countries and the second GET request using a list of country ISO codes to filter the request to get a list of cities for those countries.

At first we will need to modify our Countries query to return the list of country codes:

power query convert to list
Figure 23: Convert country ISO code to list

Select Column1.code column and click on the "Convert to List" button as highlighted above:

power query convert to list
Figure 24: Country ISO code list

The M script is as follows:

let     
    Source = Json.Document(Web.Contents("https://api.openaq.org/v1/countries")), 
    results = Source[results], 
    #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"code", "count", "locations", "cities", "name"}, {"Column1.code", "Column1.count", "Column1.locations", "Column1.cities", "Column1.name"}), 
    #"Column1 code" = #"Expanded Column1"[Column1.code] 
in 
    #"Column1 code"

This query output now can be used to feed data into our parameter list. Right click on queries and select to create "New Parameter":

power query parameters
Figure 25: Parameter ParamCountryCodeList

Now we can use this parameter to pass values from one GET request to another.

Both GET requests have to be part of the same query otherwise the advanced query editor will return an error.

Here is the M script for all cities that are found in the list of countries returned by the first GET request:

let     
    Source = Json.Document(Web.Contents("https://api.openaq.org/v1/countries")), 
    results = Source[results], 
    #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"code", "count", "locations", "cities", "name"}, {"Column1.code", "Column1.count", "Column1.locations", "Column1.cities", "Column1.name"}), 
    #"Column1 code" = #"Expanded Column1"[Column1.code], 
    ParamCountryCodeList = #"Column1 code", 
    cities_url = "https://api.openaq.org/v1/cities", //Web Url 
    request_limit = "10000", //number represented by a string 
    SourceCities = Json.Document(Web.Contents(cities_url, [Query = [limit = request_limit, country = ParamCountryCodeList]])), 
    // number represented by a string 
    ListOfRecords = SourceCities[results], 
    #"Converted to CitiesTable" = Table.FromList(ListOfRecords, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
    #"Renamed Columns" = Table.RenameColumns(#"Converted to CitiesTable",{{"Column1", "Cities"}}), 
    #"Expanded Cities" = Table.ExpandRecordColumn(#"Renamed Columns", "Cities", {"country", "name", "city", "count", "locations"}, {"Cities.country", "Cities.name", "Cities.city", "Cities.count", "Cities.locations"}) 
in 
      #"Expanded Cities" 

or as shown in the Advanced Editor:

power query advanced editor
Figure 26: M-script multiple GET requests

The Cities table in the advanced editor should look like this:

power query data
Figure 27: Cites table only found in the list of countries

Conclusion

The Power Query M formula language is an excellent tool when one has to get data direct from an API source to Power BI. It is of course no match when it comes to functionality against backend tools like SSIS, ADF or Logic Apps that possess a lot more power to manipulate API calls. But, with both graphical and code based interfaces to configure the transformations it is not far behind and this allows for quick and easy data transformations.

Consider Power Query M formula language for your next small Analytics project before you jump into a lot of heavy backend programming.

Next Steps


Last Updated: 2020-02-12


get scripts

next tip button



About the author
MSSQLTips author Semjon Terehhov Semjon Terehhov is an MCSE in Data Management and Analytics, Data consultant & Partner at Cloudberries Norway.

View all my tips





Comments For This Article




Monday, August 10, 2020 - 2:35:20 PM - Semjon Terehhov Back To Top (86268)
Sergio,

Glad to hear your feedback on this tip. Could you try another request limit? Word of warning. The API interface is dynamic and it could have very well changed since I have written this article. It could be worth while to check with https://api.openaq.org documentation for latest changes. If you send me more details in the comments I will be able to help you better.

/Semjon

Friday, August 07, 2020 - 10:43:53 PM - Sergio Hidalgo Valverde Back To Top (86255)
Hi,

Thanks a lot for your post. I have a problem, the request_limit = "1000" is not working with my case. Could you help me? The query just take 100 records, so, my line is not working.

Regards,

Sergio Hidalgo V
From Costa Rica


download





Recommended Reading

Power BI Histogram Example using DAX

Using Power BI with JSON Data Sources and Files

Calculating MTD, QTD, YTD, Running and Cumulative Total in Power BI

Power BI Workspace Permissions and Roles

Deploy Reports from Development to Test to Production using the Power BI Deployment Pipelines








get free sql tips
agree to terms