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!