How to use Youtube API from Power Query – Part 1

2016-01-24 11_59_54-Book1 - Excel

Google offer quite a comprehensive API for Youtube but I haven’t seen any examples of people using Power Query to explore.

So let’s do that – we’re going to use Power Query to access Youtube’s API and display a simple table of popular videos such as the above.

Before you can do this, you will need an API key for accessing the Youtube API.

Google provide instructions for this here.

The key steps are:

  1. Create a new project in the Google Developers Console.
  2. Turn on access for the YouTube Data API v3.
  3. Create a browser API key.

The end result should be a long string of random characters constituting your key for accessing the Youtube Data API.

Now, we can get into Excel and Power Query.

Usually when you are accessing an API in Power Query you would use Web API authentication.  This doesn’t seem to work for Youtube Data API, as it expects the API key to be provided in the query not the header, so we will need to specify our API key manually.

Start with a Blank Query and paste the following into the Advanced Editor:

Update 28/3/2016

You may get a response indicating that you need to specify how to connect or otherwise enter credentials.  If you have already entered credentials you may need to edit them in order for future steps to work.

2016-03-28 10_00_00-Query2 - Query Editor

Click to Edit Credentials.

Then you need to indicate that you want to connect anonymously.  This is because we are providing our credentials (our API key) as part of our Query string not via the Query header.

So when the below window appears, make sure you select Anonymous on the left and select just the Youtube search hyperlink radio button on the right and finally hit Connect.

2016-03-28 10_00_25-Query2 - Query Editor

Expected result:

yt 1

The Youtube API documentation explains in detail what our query actually does, but essentially we are performing a search for the top 50 (by number of views) videos containing the term “power query”.

You could actually enter this directly into your browser if you wanted to. It is equivalent to:

https://www.googleapis.com/youtube/v3/search?key=<your api key here>&q=power%20query&part=snippet,id&maxResults=50&order=viewCount

 

Now, if you click on the items List.  Power Query will return a list of the results as records.

2016-01-24 11_36_30-Youtube Search List Merge - Query Editor

They will be easier to work with as Table so next we’ll convert to a Table.

2016-01-24 11_42_07-Youtube Search List Merge - Query Editor

Returning this:2016-01-24 11_36_52-Youtube Search List Merge - Query Editor

If you then expand out the column, you can start to see all of our data:

2016-01-24 11_37_15-Youtube Search List Merge - Query EditorNext expand out the id and snippet:

2016-01-24 11_37_35-Youtube Search List Merge - Query EditorAs you can see, we now have a list of videos that all look to be related to Power Query!

If you remove columns except for video id, publishedAt, title and channelTitle, you should end up with a result something like this:

2016-01-24 11_59_54-Book1 - Excel

We can immediately learn that ExcelIsFun is a very popular Youtube channel among Power Query enthusiasts – it has 4 videos in the top 10.  I definitely recommend checking it out.

Unfortunately, we don’t have any information about the number of views, or likes, or comments.

In order to add this information we will need to perform another query to the API asking for statistics.

I’ll be covering this in a future post.

Here’s the full code to date:

A follow up post has been added here.

19 thoughts on “How to use Youtube API from Power Query – Part 1”

  1. Hi Ken,

    I tried your method of querying youtube, but Power BI/ Power Query is still asking me to authenticate….What I am doing wrong?
    I copied your code and added my API key. When running the query I get a ribbon asking me to authenticate. Could it be because I have implemented the 2-step authentication?

    Many thanks,
    Ruth

    1. Hi Ruth,

      I don’t have 2-step authentication turned on, but nothing in the YouTube API documentation suggests that that makes a difference to API access. Are you definitely using a Browser API key? Should tell you if you look in your credentials under the API manager in Google developer console.

      I will have a closer look tomorrow.

      Cheers

      Ken

    2. Hi Ruth,

      Also it is important that you tell Power Query/BI to use anonymous authentication when accessing the Google API. I didn’t spell this out in the post.

      Can provide details as needed.

      Cheers

      Ken

      1. Hi again,

        Wonderful!!! The anonymous connection was my issue. Thanks!

        If you are travelling to Stockholm let me know and I buy you a beer 🙂

        /Ruth

      2. Can you provide these details? I’m using Power Query in Excel 2016 and I am also getting the ribbon saying “Access to the resource is forbidden.” with a button to “Edit Credentials”. Nothing I enter will allow me to access it.

        1. Hi Lucas,
          I’ll add a new post describing exactly how to deal with this. Essentially you need to select “Anonymous” tab, but I’ll show some screenshots to make it clear.

        2. Hi Lucas,

          I have updated the post to clearly describe the correct way to enter credentials. Can you let me know if this resolves your issue?

          Ken

          1. Nice, it’s working now! However, I think the reason is because initially I missed the key step 2. “Turn on access for the YouTube Data API v3.” Perhaps you should add pictures to that, because I thought it was automatically done when I created the key.

            Anyways, this is very cool, thanks! I was really looking forward to Part 2, though I don’t know how realistic it will be with the YouTube Analytics and OAuth 2.0 situation… Seems very tough to navigate.

          2. Hi Lucas

            Glad you managed to get it working. So what are you keen to see covered in a Part 2? A further exploration of the Data API or how to get Analytics API and OAuth working with Power Query?

            Thanks for input re the lack of pictures for that part of step 2. Will update when I get a chance.

          3. I’d love to be able to query particular channel or video IDs for some of the metrics given by the Analytics API (such as views, likes, etc.). When I looked into it, it definitely seemed to overwhelming >.<

  2. Hi again,
    You probably know this already, but the analytics API needs Oauth2 to authenticate, the anonymous authentication is not possible….
    Perhaps that was your Part 2?
    I would love to get youtube analytics in Power BI, but as I am not a developer, that is way too complex for me.
    Thanks for the post, I can use some of the info for other purposes. 🙂
    /Ruth

    1. Hi Ruth
      I’m sure there is a way to get it to work. I’ll see if I can come up with something later in the week.

      Glad you got at least this initial part working.

      Ken

  3. This is awesome, thank you for posting this! I am not a developer, so I apologize for the possibly dumb question: why does the query fail if I try 100 or 200 results? Does it max out at 50?

  4. Hi Ken,

    When I try this method I get an error stating “An error occurred in the ‘’ query. Expression.Error: The name ‘Source’ wasn’t recognized. Make sure it’s spelled correctly.” I’m quite new to all of this and I’m not sure what the issue is.

    Thank you!

    1. Hi TJ,

      It sounds like you may have some kind of copy and paste error. I’d double check that you’ve followed all the steps as directed.

      Regards

      Ken

Leave a Reply

Your email address will not be published. Required fields are marked *