YouTube Data Dashboard in Power BI

Creating a YouTube Data Dashboard in Power BI Using Power Query M Code

Creating a dashboard in Power BI that visualizes YouTube data such as video details, likes, comments, and views can be incredibly valuable for content creators, marketers, and data enthusiasts. In this blog, we’ll walk through how to use Power Query M code to extract data from the YouTube Data API and create a dynamic dashboard.

How to Retrieve YouTube Channel and Video Details Using Power Query M Code

Power Query M Code is a powerful language for data transformation and is widely used in Power BI. In this blog, we will explore how to use Power Query M to retrieve details from YouTube channels and videos using YouTube’s Data API. We’ll cover how to extract channel details like the channel name, description, profile image, and subscriber count, as well as video details like title, description, thumbnail, views, likes, and comments.

We’ll be focusing on two popular YouTube channels: Cristiano Ronaldo’s official YouTube channel (@cristiano) and MrBeast’s channel. Let’s break this process down step by step.

Step 1: Setting Up the API Key

Before we dive into the code, ensure that you have your API key ready. You can obtain an API key from the Google Cloud Platform by enabling the YouTube Data API v3. This key will allow you to make requests to the YouTube Data API to retrieve information.

Part 1: Retrieving YouTube Channel Details

To retrieve details from YouTube channels, we use the channels endpoint of the YouTube Data API. Here’s the Power Query M code that extracts the channel details:

let
    API_KEY = "YOUR_API_KEY",
    ChannelIDs = {"UCtxD0x6AuNNqdXO9Wp5GHew", "UCX6OQ3DkcsbYNE6H8uQQuVA"},
    GetChannelDetails = (channel_id as text) =>
    let
        URL = "https://www.googleapis.com/youtube/v3/channels?part=snippet,statistics&id=" & channel_id & "&key=" & API_KEY,
        Source = Json.Document(Web.Contents(URL)),
        Items = Source[items]{0},
        Snippet = Items[snippet],
        Statistics = Items[statistics],
        ChannelName = Snippet[title],
        ChannelDescription = Snippet[description],
        CustomURL = try Snippet[customUrl] otherwise "N/A",
        ProfileImage = Snippet[thumbnails][high][url],
        Subscribers = Number.FromText(Statistics[subscriberCount]),
        ChannelThumbnail = Snippet[thumbnails][high][url]
    in
        [ChannelName=ChannelName, ChannelDescription=ChannelDescription, CustomURL=CustomURL, ProfileImage=ProfileImage, Subscribers=Subscribers, ChannelThumbnail=ChannelThumbnail],
    
    ChannelDetails = List.Transform(ChannelIDs, each GetChannelDetails(_)),
    Table = Table.FromRecords(ChannelDetails)
in
    Table

Explanation:

1. API Key: Replace "YOUR_API_KEY" with your actual API key.
2. Channel IDs: The ChannelIDs list contains two channel IDs: Cristiano Ronaldo’s channel (@cristiano) and MrBeast’s channel.
3. GetChannelDetails Function: This function takes a channel ID and constructs a URL to call the YouTube Data API’s channels endpoint. It retrieves the channel’s snippet and statistics parts.
4. Data Extraction:
   • ChannelName: Retrieves the name of the channel.
   • ChannelDescription: Retrieves the description of the channel.
   • CustomURL: Gets the custom URL of the channel (if available).
   • ProfileImage: Retrieves the URL of the high-resolution thumbnail image of the channel.
   • Subscribers: Retrieves the subscriber count of the channel.
   • ChannelThumbnail: Another way to get the thumbnail image URL.
5. ChannelDetails Table: The List.Transform function loops through each channel ID, calls the GetChannelDetails function, and collects the results in a table format.

Part 2: Retrieving YouTube Video Details

Now that we have the channel details, let’s move on to retrieving details about the latest videos from each channel. We’ll use the search endpoint of the YouTube Data API to get a list of recent videos and the videos endpoint to get statistics for each video.

let
    API_KEY = "YOUR_API_KEY",
    ChannelIDs = {"UCtxD0x6AuNNqdXO9Wp5GHew", "UCX6OQ3DkcsbYNE6H8uQQuVA"},
    MaxResults = 10,
    GetVideoDetails = (channel_id as text) =>
    let
        URL = "https://www.googleapis.com/youtube/v3/search?part=snippet&channelId=" & channel_id & "&order=date&maxResults=" & Number.ToText(MaxResults) & "&key=" & API_KEY,
        Source = Json.Document(Web.Contents(URL)),
        Items = Source[items],
        Videos = List.Transform(Items, each [
            VideoTitle = _[snippet][title],
            VideoDescription = _[snippet][description],
            VideoThumbnail = _[snippet][thumbnails][high][url],
            VideoID = _[id][videoId]
        ]),
        VideoIDs = List.Transform(Videos, each [VideoID]),
        VideoStats = List.Transform(VideoIDs, each GetVideoStatistics(_)),
        MergedList = List.Zip({Videos, VideoStats}),
        Combined = List.Transform(MergedList, each Record.Combine(_))
    in
        Combined,
    
    GetVideoStatistics = (video_id as text) =>
    let
        URL = "https://www.googleapis.com/youtube/v3/videos?part=statistics&id=" & video_id & "&key=" & API_KEY,
        Source = Json.Document(Web.Contents(URL)),
        Statistics = Source[items]{0}[statistics],
        Likes = Number.FromText(Statistics[likeCount]),
        Comments = Number.FromText(Statistics[commentCount]),
        Views = Number.FromText(Statistics[viewCount])
    in
        [Likes=Likes, Comments=Comments, Views=Views],
    
    VideoDetails = List.Combine(List.Transform(ChannelIDs, each GetVideoDetails(_))),
    Table = Table.FromRecords(VideoDetails)
in
    Table

xplanation:

1. MaxResults: This variable defines the maximum number of videos to retrieve per channel (set to 10 in this case).
2. GetVideoDetails Function: This function takes a channel ID and constructs a URL to call the search endpoint. It retrieves the snippet part for each video.
3. Extracting Video Information:
   • VideoTitle: Retrieves the title of the video.
   • VideoDescription: Retrieves the description of the video.
   • VideoThumbnail: Retrieves the URL of the high-resolution thumbnail image of the video.
   • VideoID: Retrieves the ID of the video.
4. GetVideoStatistics Function: Takes a video ID and calls the videos endpoint to get statistics like views, likes, and comments.
5. Combining Results: The List.Zip function merges the video snippets and statistics, and Record.Combine combines them into a single record.
6. VideoDetails Table: The List.Transform function loops through each channel ID, calls the GetVideoDetails function, and combines all results into a table.

Conclusion

With the above Power Query M code, you can retrieve both YouTube channel details and video details for any YouTube channel by just providing their Channel IDs. This is a great way to dynamically pull data into Power BI or Excel, allowing for detailed analytics and visualization of YouTube channel performance.

Feel free to replace the ChannelIDs with any other YouTube channel’s ID that you want to analyze. Happy data analyzing!