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.