How to get and automatically refresh APIs data

Leonardo Karpinski

Leonardo Karpinski

Power BI Master and Microsoft Certified Trainer, owner of the Power BI Experience. Graduated more than 20,000 students and participated in extensive projects for global companies.

Hey guys!! How are you doing??

Today’s topic is about how to get and automatically refresh data from APIs (a subject I’m always asked about), which I talked about on Live # 22! It’s a more dense and technical subject, as it involves a little programming! However, not so difficult that you can’t develop. I assure you this is worth reading since there aren’t many contents that explain it in a practical way.

Straight question: “What is API?”. If you don’t know, follow along and you will understand a lot about the subject!

In this post I’ll explain:
– What is an API
– Demonstration cases (Trello, WheatherAPI, CRM Pipe Run): ways of authentication and how to test
– Tips for study material

What is an API?

To help explain what an API is, I’m going to use our good old Google:

Image 1: Search on the browser

In short: it is when we have a software (software company) and we have a system with a database within it. You want your user to have access to that database. In order not to create a user for each of your clients and have all the work of technical knowledge, permissions (for example firewall). The solution is to provide the client with an interface in which a database may be reached programmatically (there is programming behind it).

Tip:
In this example link I have the most complete description of what API is: https://canaltech.com.br/software/o-que-e-api/

Trello

An example is Trello. Trello is a website where you can assemble flows (projects, tasks, etc.) through cards, it looks like a Kanban. For those who don’t know, I will show an example:

Image 2: Trello website

Image 3: Task cards

We are able to access the information on these cards through the Trello API (programming). Trello’s developers created this interface in order to make information available to their users.

Is this clear? Did you understand the main idea of ​​the API?

Now, imagine if anyone could access data through that interface. It wouldn’t be cool, right? Customers would be unlikely to continue using this solution. That’s why there’s authentication! Which means, the data is private.

To reach this data we have the following ways:

– API Key in the URL
– Token
– Username and password

In this article, I will show you 2 of these ways.

It’s important to mention that to work with and learn about APIs you will have to be persistent and curious! That’s the only way to develop on this subject. I say this because it’s unlikely there’ll be someone on the company’s side who’ve created the solution and is able to teach you.

Back to our Trello example, let’s look for Trello API documentation in the browser:

Image 4: Broswer search

Image 5: API explanation environment

Image 6: Access to the API key area

For this case, I’m working on a test account. I’ll discard this key as soon as we finish the explanation. However, it is very important that you don’t pass on your API key, as it’s an authentication item that can be used for improper access!

Image 7: Requesting the API key

Image 8: API key

Well, we have the key! One more item is missing: the Token. For the token, just click on the icon in the same window:

Image 9: Enabling permission through Token

Image 10: Token

Generally, in good documentation, you can test calls in the documentation itself! I will show in a later example.

For the Trello example, we have 3 main calls (resources):

– Boards
– Lists
– Cards

To make the boards all, we’ll use the following general URL:

Call:
https://api.trello.com/1/members/me/boards?key={yourKey}&token={yourToken}

Note the terms “yourKey” and “yourToken“. This is the information that we must exchange for the information we just obtained. So, our call is:

Call:
https://api.trello.com/1/members/me/boards?key=0e71dd15b0d63c3aa740cef8347044bc&token=172ca9399cbcd8235d9740a4a30f98d4ee66f1c84777e7372a92f25e9df12a97

With the call copied, we go to the browser and paste it to be able to access the information:

Image 11: Call result in the browser

The callback is a JSON with all the properties of the boards/lists/cards. Do you remember the pictures on the home screen of my environment on the Trello website? There were two cards there, I will show a part of the code of each one with the card next to it:

Image 12: Code and cards visual

Well, is it always possible to make a call using the browser? No! This way worked because the authentication is in the URL itself. There are other ways, which you can’t get via the browser and you’ll have to use Postman (calm down, I’ll show you later).

With this first contact, we can move on to Power BI. I want to explain that I don’t consider making the call directly through Power BI the best solution (just as it is not the best option to connect Power BI directly to your transactional database). It’s more efficient to have a staging layer and a Data Warehouse for development. This reduces risks and makes the call more performative!

In Power BI, go to “Get data“:

Image 13: Getting data from Web

Image 14: Informing the call URL

Steps: 
Open a new file in Power BI Desktop → In "Home" click on "Get data" → Select "Web" → In the new box paste the URL for the boards call

With that done, the Power Query environment will open with the code in list form for each card:

Image 15: Codes in list form

Image 16: Transforming the lists into tables

Image 17: Expanding the Table

Image 18: Expanded Tables and changed name

Steps: 
1. In "Transform" click on "To Table"
2. Click the expansion arrow next to the column name → Disable "Use the original column name as a prefix"
3. Change the query name

Ready! We have the information from the boards. To use dynamic references in the process and be able to use the information more than once, we will create 3 parameters:

– Token
– API key
– idBoard

Image 19: Creating Parameters

Image 20: Token Parameter

Image 21: Key Parameter

Image 22: idBoard Parameter

Steps:
1. In "Home" click on "Manage Parameters"
2. Change "Name" to "Token" and "Current Value" to our Token value → Click "New"
3. Change "Name" to "Key" and "Current Value" to our Key value → Click "New"
4. Change "Name" to "idBoard" and "Current Value" to paste the value of the first id

To optimize the boards call, I will use 2 devices within the “source” to reference the URL to the Token and Key parameters:

Image 23: Accessing the “Source”

Image 24: Modifying the Source

Steps:
1. In "Boards" click on "Source" within the "Applied steps" field
2. Change the formula
Formula:
= Json.Document(Web.Contents("https://api.trello.com/", [RelativePath = "1/members/me/boards", Query = [key = Key, token = Token]]))

This step is very important because only through it you will be able to make it work in Power BI Online. Even if it’s working in Power BI Desktop when we publish it would be a problem in Power BI Online.

Now, for Lists we will take advantage of the query made for Boards and duplicate it:

Image 25: Lists query created

Image 26: Removing other queries

Image 27: Lists result

So far, we only have the primary information (idBoards) to invoke the other Lists columns. We will invoke with the help of a function: GetLists! I will provide the ready function and copy it to our file:

Image 28: Function copied to the file

GetLists function:
= (idBoard as text) => let
Fonte = Json.Document(Web.Contents("https://api.trello.com", [RelativePath = "/1/boards/" & idBoard & "/lists", Query = [key = Key, token = Token]]))
in
Fonte

This formula calls lists from a specific Board (so we need the id). Now in the Lists query, let’s add the new columns:

Image 29: Copied function

Steps:
In "Add Column" click on "Invoke Custom Function" → Select "GetLists" in "Function Query"

Image 30: Expanding the table with the added columns

Steps:
Click the expansion arrow in the corner of the "GetLists" column → Click the expansion arrow in the corner of the "GetLists" column

Image 31: Expanded Table and List reference in the Trello website

Note that in the formula row we have which columns are present in the table. And in the image, for each card we have (id) the lists present on them.

Continuing, we will create the Cards query. The process is the same as we did to create the Lists query (duplicate boards, leave only the id column, and invoke the other columns through a function):

Image 32: Creating Cards query

Image 33: Creating GetCards function

Steps:
1. Duplicate the Boards query → Select column "id" → Right-click → Select "Remove other columns" → Rename query to Cards
2. Duplicate GetLists function → Rename to GetCards → Change the term
GetCards Formula:
= (idBoard as text) => let
Fonte = Json.Document(Web.Contents("https://api.trello.com", [RelativePath = "/1/boards/" & idBoard & "/cards", Query = [key = Key, token = Token]]))
in
Fonte

Image 34: Invoking columns through the function

Steps:
In "Add Column" click on "Invoke Custom Function" → Select in "Function Query" the "GetCards"

Image 35: Expanding the table

Image 36: Expanded table

All set! Expanded table … cool, right? But, does it work? I’ll change a card there on the website and see if by refreshing our Power Query the values ​​change:

Image 37: Refreshing test in website

Steps:
Evaluate the "Name" column and its cards → Add the "Testing new card" card on the Trello website → Refresh Power Query → Evaluate the "Name" column with the new present card

There you go! With the test done, I can move on. I will close and apply and go to the Power BI environment.

Image 38: Close and Apply

The first step in Power BI is to create the relationships. Our hierarchy will be:
Cards → Lists → Boards

Image 39: Model Relationships

Are the relationships working? To test, let’s create a table:

Image 40: Table visual

Steps:
In "Views" select "Table" → Drag the fields "name", "id.1" from Lists and "id.1" from Cards to values 

It worked! The contexts are working according to the created relationships. Now, I will do one more test and create a new list and a new card for the “Live Mestre Power BI” Board:

Image 41: List and card creation

Image 42: Power BI refresh

Steps:
Creation of new list and new card in Trello → Rename ids in "Values" → In "Home" click on "Update"

Again, it worked! In the image, see that we have an additional list and a card for “Lives Mestre Power BI”. We finished development on Power BI Desktop, now just save the file and publish:

Image 43: Saving the file

Image 44: Publishing the file

Steps:
1. Click on "Save" → Choose project location and name
2. In "Home" click on "Publish" → Choose the workspace for publication

A lot of people have a problem with APIs in the Power BI Online environment in terms of the credentials of the data source. This may be due to not using Relative Path and Query. Let’s test and see if we have problems?

Image 45: Opening the credentials environment

Image 46: Entering credentials into the source

Since there is no error after “Enter”, the update is working!

You may be wondering why it works with Relative Path. This happens, because credential authentication in Power BI Online needs to be dynamic (we have more than one board, right?) And if we don’t use Relative Path we don’t have that dynamism. The Base URL is static.

Weather API


WheaterAPI is a free website where we can obtain weather information.

Tip:
For more information about the Weather API, visit the link:
https://www.weatherapi.com/about.aspx


I’m going to open my user home page in the browser, and let’s explore the information:

Image 47: Account home page


So, does it look familiar? Very similar to the information we get at Trello, right? Let’s enter the explorer and make a call with the API key and the city of Florianópolis:

Image 48: API Explorer

Image 49: Explorer


We can test the call in the browser, just as we did for Trello:

Image 50: Test on the browser

Call:
https://api.weatherapi.com/v1/current.json?key=859398197c5e49ee98015704201606&q=Florianopolis


In Power BI, we will obtain the data through the call:

Image 51: Getting data with the API key

Image 52: Informing the API key

Then, in the Power Query environment, we transform the query:

Image 53: Transforming the query

Steps:
1. Open a new file in Power BI Desktop → In "Home" click on "Get data" → Select "Web" → In the new box paste the URL call
2. In "Convert" → click "In Table"

For this example we will use 2 parameters:

Image 54: Creating the parameters Key and Local

Steps:
1. In "Home" click on "Manage Parameters"
2. Change "Nome" to "Key" and "Valor Atual" to our key value → Click on "Novo"
3. Change "Nome" to "Local" and "Valor Atual" to Florianopolis

With the parameters created we can modify our query source, which is static:

Image 55: Changeing the static source

Steps:
Change the query source formula
Formula:
= Json.Document(Web.Contents("http://api.weatherapi.com/v1/current.json?key=" & Key & "&q" & Local))

We’ll transform this query into a function with the help of Power Query:

Image 56: Creating function from query

Steps:
Right-click on the query → Click on "Criar função..." (create function) → Name the function

Image 57: Modifying the function

Steps:
Change the function formula
Formula:
= (Local as text) => let letJson.Document(Web.Contents("http://api.weatherapi.com/v1/current.json?key=" & Key & "&q" & Local)) in Fonte

Everything ok so far? If yes, great, if not …. persistence! Go back in the text, read again until you understand. We are not normally familiar with this subject, so dedication is required to learn.

We have the function! Shall we test it? For this, I will create a city list and pull the information for each one:

Image 58: Creating the table “Cidades” (Cities)

Image 59: Created table

Steps:
In "Home" click on "Insert Data" → Name the table and column → Insert information

Image 60: Invoking the function

Image 61: Expanding the columns

Image 62: Expanded query and deleted queries

Steps:
1. In "Add Column" click on "Invoke Custom Function" → Select the function GetWeather
2. In the right corner of the "GetWeather" column click expand → In the corner of the "current" column click expand
3. Delet the queries "current" and "Local (Florianopolis)

All set, right? Is it? Go back to the GetWeather function and compare it to the functions we used in the Trello example.

So, did you find any difference? There is a difference, right? We didn’t use Relative Path! And I talked a lot about its importance … I did it on purpose to see what happens in Power BI Online if we don’t use that argument.

Let’s save the file and look at the data source settings (an error sign will be displayed):

Image 63: Saving the file

Image 64: Checking the data source settings

Image 65: Publishing the file

Steps:
1. Salve the file
2. In "Home" click on the arrow under "Transform data" → Click on "Data source settings"
3. In "Home" click on "Publish" → Choose the workspace  

In the Power BI Online environment, let’s check how the API works:

Image 66: Entering the data source credentials environment

Image 67: Error, we can’t schedule the data refresh

Steps:
1. Select the workspace → Select "Data source + data flow" → Click on "Schedule refresh"
2. Verify that the refresh can't be scheduled

The way out here is to go back to our GetWeather function in Power Query and use Relative Path:

Image 68: Editing the GetWeather function

Image 69: New function

Steps:
Click on "Advanced Editor" → Change the formula
Formula:
= (Local as text) => let
Fonte = Json.Document(Web.Contents("http://api.weatherapi.com/v1", [RelativePath = "/current.json", Query = [key = Key, q = Local]]))
in
Fonte

Image 70: Result without errors

To make it work in Power BI Online, you must: update the file, save and publish again. That done, just refresh the same page that were in Power BI Online:

Image 71: Now the scheduled refresh is allowed

The last step is to assess the test connection. Let’s open the credentials:

Image 72: Edit Credentials

Image 73: Teste connection ignored

Steps:
Refresh the Power BI Online page → Click on Data source credentials → Click on Edit credentials

This option is new in Power BI Online! If it were the old way, we would have a problem with the refresh. What happens here is that Power BI tests our Base URL (http://api.weatherapi.com/v1) if this box is unchecked. We will access our Base URL in the browser to see the return:

Image 74: Test with the Base URL


The page access return is error 404, let’s test in Power BI Online by unchecking the option to skip the test:

Image 75: Refresh error

Well, now what? What to do? Easy, we still have to work on the GetWeather function formula. Let’s test the URL http://api.weatherapi.com in the browser:

Image 76: URL test

Good, we have a positive return! So, what if we test this URL as the basis of our function? We have a possible solution:

Image 77: GetWeather modification

Steps:
1. Test Base URL on the browser
2. Test the test connection
3. Test new URL on the browser
4. Change the GetWeather formula
Formula:
= (Local as text) => let
Fonte = Json.Document(Web.Contents("http://api.weatherapi.com", [RelativePath = "/v1/current.json", Query = [key = Key, q = Local]]))
in
Fonte

Again, update the file, save and publish to Power BI Online! At Power BI Online, let’s make the test connection:

Image 78: Teste connection working

Success! Our authentication working and ready to schedule the refresh!

CRM PipeRun (Postman)

CRM Piperun is a very interesting, lightweight and practical system for sales funnel controlling. To access their database, we also use APIs. However, unlike Trello and WeatherAPI systems, authentication is done through a Token passed in the Header (hence the need for Postman). Well, let’s go to the example and you will understand better.

Tip:
For more information about CRM PipeRun , access the link: https://crmpiperun.com/

Image 79: Account Home Page

Just like we did for other examples, let’s look at their API documentation and find out how to make the calls:

Image 80: Accessing the API documentation

Image 81: Getting the deals call

Steps:
1. Search for "piperun documentation api" in the browser → Click on the first link → In the tab that opens click on the new link
2. Select "Get Deals → Copy the call
Call:
http://api.pipe.run/v1/deals 


Well, what do we do with this call? We test it …. and for that we paste it in the browser:

Image 82: Unsucsseful test

To test the CRM PipeRun call, we need to use Postman (unlike Trello and Weather API):

Image 83: Access to Postman

Image 84: Testing the call

In addition to the call, we need the Token that we will obtain on the website:

Image 85: Obtaining the Token in the website

Image 86: Structure for Request in Postman

Image 87: Header and call tested

Steps:
1. Copy the call in the browser
2. Open Postman → Paste the call → Click "Send"
3. Access the "Central de Integrações" (Integration Central) on the CRM PipeRun website → Copy Token
4. In Postman, click on "Headers" → Fill in th "Token" in Keys and inform the value in "Value" 

With the authentication tested in Postman, we can move on to the next step. Let’s get data in Power BI in a new file:

Image 88: Getting data in Power BI

Steps:
In "Home" click on "Get Data" → Select Web → Select "Advanced" → Insert URL, token and the token value

In the Power Query environment, we have something similar to the other cases:

Image 89: Query in Power Query

Now I’m going to test a possible Base URL that works in the browser, so I don’t have any problems with a test connection in Power BI Online. First I will test for http://api.pipe.run/v1:

Image 90: URL Test

It didn’t work, let’s test http://api.pipe.run:

Image 91: URL Test

It worked! Great, we already have our Base URL to build the formula. Let’s add the Relative Path:

Image 92: Change in formula

Steps:
Change the source formula
Formula:
= Json.Document(Web.Contents("http://api.pipe.run", [Headers=[token=b3512ff32f60af1edbd6f62e800b754d], RelativePath = "/v1/deals"]))

Shall we expand to verify the information?

Image 93: Expanding the query

Steps:
Click on "List" → Click on "Convert to table" → Click the button to expand in the right corner of the "Column1" column

Image 94: Expanded table

Cool, we have the result of the table with the two deals (Consultoria PBI and Curso PBI) from the CRM PipeRun website. Test time! I’ll create a deal there on the website and refresh Power Query:

Image 95: Deal creation

Image 96: Power Query refreshed

Finally, let’s create the parameter for the Token and insert it into the source formula:

Image 97: Parameter creation

Image 98: Formula change

Step:
1. Create test deal in theCRM PipeRun website
2. Refresh Power Query
3. In "Home" click on "Manage Parameters" → Change name, type and value
4. Change source formula
Formula:
= Json.Document(Web.Contents("http://api.pipe.run", [Headers=[token=Token], RelativePath = "/v1/deals"]))

Tips: Pagination and Relative Path

Pagination is a topic that I manage to develop, but I still don’t feel comfortable in a didactic way. For this matter I prefer to recommend the material I use to solve the problems I have in projects.

Find below some support links for pagination:

As to Relative Path and connection test I recommend Chris Webb, who in my opinion is the person who most understands the subject in the community:

Guys, thank you to those who followed along until the end of the post !! This subject is very dense, and I had to try my best to be able to share it with you on Live # 22. It is really complex.

Hope I have made it clear and that you learned from it. My suggestiong for knowldge fixing is to go back to the topics where where you struggled, reread, access the pages I recommended at the end of the post and try understand the details. And of course, practice a lot !!!!! Much of what I did here you can replicate by creating test accounts on the websites.

Thanks everyone!

Regards,
Leonardo

Share this post:
Share on facebook
Share on linkedin
Share on twitter
Share on pinterest