Last week we were given the task to independently learn how to connect to APIs in Power BI. First this blog will walk through a simple method to connect to a API that requires no authentication, pagination or parameter pathways. Then I will dive in to the M Code I wrote that allowed me to connect to an API that required pathway parameters.

 

Connecting to Basic APIs

 

Of course before you connect to an API, it is essential to first read the documentation to check the base URLs, possible endpoints, and if there is any authentication or pagination required for the data. Once you select the specific endpoint you want to connect to, you copy the GET /”x” URL, open up Power BI, go to Transform Data, add a New Source as “Web”, and then paste the URL. Power BI will then automatically generate a table for you (rather than you having to change from a list to table, then expand the records manually).

 

Connecting to APIs with Path Parameters

 

For my task, I decided to have a bit of fun with it and connect to a Taylor Swift API that I found online (I thought it would also be timely as she was landing in Australia for her tour on the day I did this). There were only 3 main endpoints, those being albums, songs, and lyrics for each song. The API didn’t require authentication and no pagination, but the lyrics endpoint required a path parameter to obtain the date. Here are the URLs:

  • https://taylor-swift-api.sarbo.workers.dev/albums
  • https://taylor-swift-api.sarbo.workers.dev/songs
  • https://taylor-swift-api.sarbo.workers.dev/lyrics/{songID}

 

From the songs table, the table contained unique songID’s for each song (177 in total). Since the lyrics are directly correlated to a specific song, the songID is needed to pull specific sets of lyrics. So the pathway parameter is the songID, in which the songID needs to change to pull a new set of data. However, it would take a long time to manually connect to 177 endpoints, so I wrote some M code to automate the process and pull all the lyrics for all 177 songID’s in one go.

 

M Code

 

Essentially, this M Code is just connecting to the API endpoint, and pulling the list of lyrics based upon a set of pre-defined variables.

= let
 baseUrl = "https://taylor-swift-api.sarbo.workers.dev/lyrics/",
  maxSongID = 177,
   songIDs = List.Numbers(1, maxSongID),
    getLyrics = (songID as number) => let songIDText = Text.From(songID), 
     apiUrl = baseUrl & songIDText, 
      response = Json.Document(Web.Contents(apiUrl)), 
       lyrics = response[lyrics] in [SongID = songID, Lyrics = lyrics],
        lyricsList = List.Transform(songIDs, each getLyrics(_)),
         lyricsTable = Table.FromRecords(lyricsList)
in
lyricsTable

 

I like to think of these more complex M code scripts from the bottom up. Let’s break each section down.

 

First, we are creating a table from the records we pulled. The records we pulled were in the form of a list, which consists of one SongID and one set of lyrics. We essentially then attribute the lyrics to a specific song ID, and use the response (Json.Document) to access the API. However, since we don’t have a apiUrl, we need to determine what the response is connecting to, which is just the baseUrl & songIDText. The songID needs to be in text form, so we essentially are saying, in order to get the lyrics for a songID, we need to use the songID but as a text (not number). In order to have a list of all songs (starting at song 1), we need to generate a list of numbers. But before we do that, we need to establish what the maximum songID is (177), In M Code, when we type 177 in, it reads it as a number, so that is why we needed to change it to text. Finally, we need to actually have the URL endpoint for the API and the baseURL.

 

And There You Have It!

 

I really enjoyed working out how to set up this API call for this project, I definitely feel I have a better understanding not only of APIs, but also how to connect to one in Power BI. I hope this is helpful for anyone trying to connect to an API endpoint that requires pathway parameters!

 

Ben Devries
Author: Ben Devries

Ben graduated with a Bachelor of Music Performance (Honours) from the Sydney Conservatorium of Music in 2023. For the last few years, Ben spent his time working as a professional jazz saxophonist which led him all around the world performing in cities such as London, San Fransisco, and of course, Sydney. But despite his musical background, Ben’s interest in data analytics came from his passion for problem solving and understanding the little details of how and why things work. From there, Ben went on to discover the Data School Down Under, and throughout the interview process became further inspired not only by the logic and flexibility of data, but also the ability for data to provide valuable insights to help solve complex business problems and present meaningful stories. Ben is excited to join Data School Down Under, and hopes to utilise his creativity, improvisational skills, and ability to draw connections upon diverse areas of information learnt as a musician within his new career in data analytics. In his spare time, Ben still enjoys playing his saxophone, as well as downhill longboarding, and spending time with his family.