Connect to the Message Center in Power BI

 

You may need to connect to the message center in Power BI to display messages for a certain product.

The first step is to enable Message Center sync to Microsoft Planner. Once this is in place, you can connect Power BI to Planner and pull in the messages. The trick is to connect an OData connection to: 

https://graph.microsoft.com/v1.0/planner/plans/{Plan ID}

Once you do that, you can manipulate the data into something that you want to display. Here is what I did:

The next step I did was to pull in the planner buckets, so I could make the bucket IDs display meaningful information. I named the query "Message Center Buckets"

let
    Source = OData.Feed("https://graph.microsoft.com/v1.0/planner/plans/{Plan ID}", null, [Implementation="2.0"]),
    buckets = Source[buckets],
    #"Removed Columns" = Table.RemoveColumns(buckets,{"orderHint", "planId", "tasks"})
in
    #"Removed Columns"

Then I created a second query, which merges in the query with the bucket names. It also filters results by "Microsoft Copilot"

let
    Source = OData.Feed("https://graph.microsoft.com/v1.0/planner/plans/{Plan ID}", null, [Implementation="2.0"]),
    tasks = Source[tasks],
    #"Merged Queries" = Table.NestedJoin(tasks, {"bucketId"}, #"Message Center Buckets", {"id"}, "Query1 (2)", JoinKind.LeftOuter),
    #"Expanded Query1 (2)" = Table.ExpandTableColumn(#"Merged Queries", "Query1 (2)", {"name"}, {"name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Query1 (2)",{{"name", "Bucket"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Bucket] = "Microsoft Copilot")),
    #"Expanded details" = Table.ExpandRecordColumn(#"Filtered Rows", "details", {"description"}, {"description"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded details",{"activeChecklistItemCount", "appliedCategories", "assigneePriority", "assignments", "bucketId", "checklistItemCount", "completedBy", "completedDateTime", "conversationThreadId", "createdBy", "dueDateTime", "hasDescription", "orderHint", "percentComplete", "planId", "previewType", "priority", "referenceCount", "startDateTime", "assignedToTaskBoardFormat", "bucketTaskBoardFormat", "progressTaskBoardFormat"}),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Removed Columns", "Message Center ID", each Text.BetweenDelimiters([description], " ", "#(lf)", 1, 0), type text),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Text Between Delimiters",{"id"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns1", "Link", each "https://admin.microsoft.com/?ref=MessageCenter/:/messages/"&[Message Center ID]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Link", type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Message Center ID", "createdDateTime", "title", "description", "Bucket", "Link"})
in
    #"Reordered Columns"

Sources:

• How to connect to Microsoft Planner to Power BI - Microsoft Fabric Community
• Message Center sync to Microsoft Planner now Generally Available - Microsoft Community Hub

Share on Google Plus

About Tom DeMeulenaere

Highly accomplished information technology professional with extensive knowledge in System Center Configuration Manager, Windows Server, SharePoint, and Office 365.