Decoding M – Exploring the YouTube Data API with Power BI

For some time now I have wanted to explore connecting Power BI with API’s, however the lack of knowledge of M, and time has held me back.

But this week I got a chance to dig a little deeper with my idea. I decided to look at Google’s YouTube Data API.  First I just want to get my head around working with API’s in Power Query or Power BI as a data source. This means getting my head around some M code.

So today I am going to go through my thought process on how the API is used get data into Power BI, and what I need to do with M to get there.

Get the YouTube Data API

To start with a unique API key is required and this can easily be obtained from Google Developer Console.  This article is going to be long enough, so I have put a very quick video together on how to access the API key.

What I know (from reading the Google YouTube Data API Documentation)

One of the things the YouTube Data API will let you do is search.  You can search videos, channels and Playlists based on a set of criteria, known as parameters

The URL is https://www.googleapis.com/youtube/v3/search

The returning data is Json format

The Maximum Results that can be returned is 50.  If you want more than this then you need the nextPageToken

The only parameter that is required is ‘part’ and the API documentation recommends that this is set to ‘snippet’

Uh, Snippet??  So I do another search in the API documentation and I find Snippet under a list of ‘Properties’.  I wonder then, where ever I need this in my code, can I replace or combine snippet with one of the other ‘Properties’?????

After this all the other parameters are optional.

You can access the full Google / Youtube Data API documentation here 

Custom Query in Power BI or Power Pivot

Yey! I’m going to use a Custom Query to get data from the API.  Why, well because I need to get my head around M and this is where it is done!

In the Get Data tab, select New Source and select Bank query

Custom Query in Power BI or Power Pivot

Then select Advanced Editor from the home Ribbon, and this will bring up the advanced query editor.  The place to write all the M code.

Decoding M - Exploring the YouTube Data API with Power BI

But wait, hold on.  Don’t do that step yet.  Because I don’t know what code to start entering. I was an Excel user, I have now spent a few years really getting the hang of DAX.  But where to start with the M code is just too far removed.  So I am going to cheat a little.

I have the URL so if I do a web query, Power BI will start to write the M code for me. After all it is after all a Self Service BI system, no programming required right!!!!

This time I will select New Query and under new Source I will select from Web and also select Advanced.

Decoding M - Exploring the YouTube Data API with Power BI

Setting up the Advanced Web query to get data from the API

I figured out earlier that the URL is https://www.googleapis.com/youtube/v3/search

This will be the first part of the URL, however as we are going to add parameters then we need to add ? to the end of the URL, making it https://www.googleapis.com/youtube/v3/search?

I also need to pass my API key.  Now I know that I can set this up as a parameter and then pass the parameter to the query but for the moment I will add it as a URL part.  The second part would be

Key=replace_this_text_with_your_api_key

From the API documentation, we also must use the parameter part and set this to snippet.  This will make our third URL part

&part=snippet

Now I can see the URL has been set to

https://www.googleapis.com/youtube/v3/search?key= replace_this_text_with_your_api_key &part=snippet

Let’s run this query and see what happens.

Power BI and Power Pivot Setting up the Advanced Web query to get data from the API

So we are returned some data, but before we look at what has been returned to us, let’s look at the advanced editor screen (the screen we spoke about earlier then did nothing in…)

Our magic M code has appeared

connecting power Bi to youtube

Decode the M code

Trying to now understand this M code.

let Source = ‘ seems to start all M codes. Why do I think this, well when we looked at the Advanced Editor earlier with no code in it, the screen did contain ‘let Source = ‘ at the beginning.  It also ended with ‘in Source’ at the end, as does the code above.

Json.Document(Web.Contents( is the next part of the code.  Making sense in my head, I did know a Json file would be returned, so by using Json.Document( must be the code to say, ‘what is after this bracket will be a Json file’. After the bracket we have ‘Web.Contents’(.  So the Json file will be found on the web, and this must have been identified by the fact I selected a Web query.

Next in the code is a URL. This seems to be a slightly amended version of the URL shown in the Advanced Web Query window.

("https://www.googleapis.com/youtube/v3/search?" & "key= replace_this_text_with_your_api_key " & "&part=snippet"))

 

Power Bi Desktop training

It appears that each part of the URL is joined.  First each part is wrapped in quotes (you might recognize this as something similar to working with text in Excel), and then joined using the & to concatenate each part.

In English this code reads to me ‘let the source file be a Json document that you will find on the web using the stated URL’

So then if I wanted to add more optional parameters, I reckon I just need to add to the M code something like

“&new parameter”

But before we test this out, let’s have a look at the data returned to us.

Extract Data in Power BI or Power Pivot from JSON

A JSON file has been returned and we now need to extract some data from it. First, you will see ‘List’ in the items kind.

Select this list and you will be returned a list of Records.  From the Transform Ribbon, select Convert to Table and select ok.

Once a table is available, the records can be expanded to show all available columns

Decoding M - Exploring the YouTube Data API with Power BI

When expanded both the id and snippet columns are also record columns that can be expanded.  When expanded, a host of information becomes available about the videos, such as title, description and channel id.

Why I was returned 5 videos, 4 of which are from Ed Sheeran, I just don’t know! 5 is the default, be Mr Ed…..

Decoding the M code used to extract data from the Json file

Right so, back to the infamous advanced query editor to look at the changes and additions in the M code now that we have extracted the data from the Json file.

Power Pivot and Youtube data api

 

The first thing I notice is a number of #.  These seem to represent a new step that I took in the transformation.

Our first transformation was converting the list to the table.  The line of code for this is

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

To me this read like create a table from a list, and with the items for the splitter, don’t use anything and if there are errors then show them as extra values.

 

Sign up for my newsletter – Don’t worry, I wont spam. Just useful Excel and Power BI tips and tricks to your inbox

Let’s look at the last transformation set, where we expanded the record column named snippet

#"Expanded snippet" = Table.ExpandRecordColumn(#"Expanded id", "snippet", {"publishedAt", "channelId", "title", "description", "thumbnails", "channelTitle", "liveBroadcastContent"}, {"publishedAt", "channelId", "title", "description", "thumbnails", "channelTitle", "liveBroadcastContent"})

This code confuses me a little.  Each of the columns now expanded have been listed twice in this code.  Here is what I think it says

Expand the records…the code then lists all the columns we expanded….then name the columns in the table….the code then lists all the columns again….

I might very well be wrong here with this understanding, but I am sure I will find out eventually….I do data transformations fairly often so I can just keep an eye on the code ( now that I am more aware!) Or someone can post a comment below with the explanation…..save me hours of research…(god I am getting lazy…is that the power of self-service?)

I have spent the time reading each line of code to try and understand M….I just explained two.  So why don’t you pause reading now and read the other lines of code with the aim of better understanding and awareness of M in Power Pivot or Power BI.

 

Playing with the M code to explore more of the YouTube Data API

 

Now that I think I understand some of the M code use by Power BI or Power Query to get data from a web API, I think I am brave enough to play with the code in the advanced query editor.

Remember earlier I said if I wanted to add more optional parameters, I reckon I just need to add to the M code something like

“&new parameter”

Well now I am going to try it out.

The YouTube Data API documentation list loads of optional parameters. Let’s look at adding some of these to the code.

I want to search for videos on my own YouTube Channel.  My channel id is UCQEO63TKG1moSUxAu07cP-A, so the line of code I will amend is the URL.  It needs to include channelId= UCQEO63TKG1moSUxAu07cP-A. Using the sample of the code already there, I need to add

&” &channelId=UCQEO63TKG1moSUxAu07cP-A”

The full M code is now

let

    Source = Json.Document(Web.Contents("https://www.googleapis.com/youtube/v3/search?" & "key=replace_this_text_with_your_api_key" & "&part=snippet" & "&channelId=UCQEO63TKG1moSUxAu07cP-A")),

    items = Source[items],

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

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"kind", "etag", "id", "snippet"}, {"kind", "etag", "id", "snippet"}),

    #"Expanded id" = Table.ExpandRecordColumn(#"Expanded Column1", "id", {"kind", "videoId"}, {"kind.1", "videoId"}),

    #"Expanded snippet" = Table.ExpandRecordColumn(#"Expanded id", "snippet", {"publishedAt", "channelId", "title", "description", "thumbnails", "channelTitle", "liveBroadcastContent"}, {"publishedAt", "channelId", "title", "description", "thumbnails", "channelTitle", "liveBroadcastContent"})

in

    #"Expanded snippet"

Once I click okay in the advanced query editor window, the good thing now is that I don’t have to redo all of the transformations.  If I just click on the last step in the Applied Steps pane then all the transformations will be done.

Again I have only been returned 5 videos, so I do a little more reading to find that a call will return 5 by default. But the maximum number is 50, so I think I will amend the code to pull in 50.  I can do this because there is an optional parameter maxResults…how awesome…….

But wait, there is also an optional parameter to order the result by viewCount.

Let’s add the following to the code

&”&maxResults=50” &”&order=viewCount”

The full code is now

let

    Source = Json.Document(Web.Contents("https://www.googleapis.com/youtube/v3/search?" & "key=replace_this_text_with_your_api_key" & "&part=snippet" & "&maxResults=50" & "&order=viewCount" & "&channelId=UCQEO63TKG1moSUxAu07cP-A")),

    items = Source[items],

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

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"kind", "etag", "id", "snippet"}, {"kind", "etag", "id", "snippet"}),

    #"Expanded id" = Table.ExpandRecordColumn(#"Expanded Column1", "id", {"kind", "videoId", "channelId"}, {"kind.1", "videoId", "channelId"}),

    #"Expanded snippet" = Table.ExpandRecordColumn(#"Expanded id", "snippet", {"publishedAt", "channelId", "title", "description", "thumbnails", "channelTitle", "liveBroadcastContent"}, {"publishedAt", "channelId.1", "title", "description", "thumbnails", "channelTitle", "liveBroadcastContent"})

in

    #"Expanded snippet"

Hehehhehehhe I can now amend M code to get data from the YouTube Data API in Power BI or Excels Power Query and I have a base code I can work with for future experiments.

To conclude…for the moment……

 The YouTube Data API is easily accessed via Power BI and Power Query in Excel with the use of a web query.  Really no M knowledge is required because all of the changes I made in the code can be made in the advanced web query window.

However I was able to use the API documentation to find parameters to explore and by reading the code, I made a successful attempt of manually changing the code in the advanced query editor.

There is so much more to explore

Here’s something you can try while you are waiting on my next article

Do a search to return 50 videos using the search term Power BI where the video was published after 1st May 2017

Post your code in the comments below and in the follow up article I will add the solution, along with a lot more exploration….

Hint…read the API Search documentation to find the parameters you require.  The only difference in the code above and the answer is the parameters.

Sign up for my newsletter – Don’t worry, I wont spam. Just useful Excel and Power BI tips and tricks to your inbox

It's only fair that you share......

Leave a Reply

%d bloggers like this: