Power BI Level: Intermediate

In this article, I am going to go through the steps needed to authenticate and pull in data from a 3rd Party application via API into Power BI.

If, like me, you've found yourself tasked with pulling data from an API with no idea what an API even is then I think this is a good place to start your troubleshooting journey. Let's start with some definitions

What is an API?

An API (Application Programming Interface) is a software that is used to enable "communication" between computer programs/applications/software. Just like other software, APIs are usually created by software developers who create code that makes it work.

there are different METHODS developers use to make requests using APIs,

GET - To retrieve data from a server, e.g you can have an API that returns a list of products and their prices

PUT/POST - used to populate or update data

DELETE - can be used to delete records

A web API is accessed through a URL which is in 2 parts

A Header with the base url, version

e.g. SavedoxxWebsite.net/api/v1

An Endpoint that acts as a pointer to the location you are pulling data from or making requests to

e.g. account/login

To make the full link: SavedoxxWebsite.net/api/v1/account/login

Please utilise our dear google for more understanding on APIs.

let's set our scenario for today.

Our company has built a cloud-based data storage application, SaveDoxx which customers use to store their files. Behind the scenes of this application, we have a large database containing all our customers data. On the SaveDoxx website, the user puts in their email and password and gets access to only their own files. We've now received a request from one of our customers, Lerox Limited. They would like to analyse their data in Power BI so they would like to export it from our database into Power BI.

We have a number of issues here

1. SaveDoxx does not have an in-built connector in Power BI so we would need an API
2. We have the data for all our clients in our database so we need to make sure Lerox Limited only receives their own data in Power BI
3. number 2 also means we have to authenticate the user first before pulling their data into power bi so we need to find a way to automate their sign in & data retrieval process.

Solution process flow

• Create your username and password as a parameter
• call GET api for authentication
• if successful, an authorisation token is generated
• the token is fed into the get api for data
• the data is imported into power bi

Step 1: Use Credentials as Parameters

1st query: Get authorisation token using log in details

Web.Contents

an example of the results of a successful API call for authentication. our aim is to retrieve the token which is stored in a JSON file format with key & value pairs. read more on that here.

in power query, open a blank query. Open the advanced editor and paste your version of this code. notice the email and and password parameters have been put into the query

let 

GetJson = Web.Contents(" https://SavedoxxWebsite.net/api/v1/account/login",
[
Headers = [#"accept"="application/json" ,
          #"Content-Type"="application/json"],
Content = Text.ToBinary("{""email"": """** & email & **""",""password"": """** & Password & **"""}")
]
),
FormatAsJson = Json.Document(GetJson),
#"Converted to Table" = Record.ToTable(FormatAsJson),
    Value = #"Converted to Table"{2}[Value],
    #"Converted to Table1" = Record.ToTable(Value),
    usertoken = #"Converted to Table1"{5}[Value]
in
    usertoken

the token i want is in the 5 value so that is why i have the number 5. adjust yours accordingly in comparison with your api results.

now we have our token

2nd Query: Pull in the data

let

source = Web.Contents("https://SavedoxxWebsite.net/api/v1/organisations/databatches",

 [Headers=[Authorization="Bearer " **&userToken**, ContentType="application/json"]]),
Result = Json.Document(source),

#"Converted to Table" = Record.ToTable(Result),
    Value = #"Converted to Table"{2}[Value],

    #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id", "quantitySold", "costPrice", "batchNumber", "organisationId", "dateCreated"}, {"id", "quantitySold", "costPrice", "batchNumber", "organisationId", "dateCreated"})
in
    #"Expanded Column1"

Hopefully, if you follow the steps and the odds are in your favour, you will be able to get your data into power bi. I'm trying to take this a bit further and create a template app built on this. still experiencing errors so will post about it when I have successfully done my troubleshooting.