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:{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"

    Source = OData.Feed("{Plan ID}", null, [Implementation="2.0"]),
    buckets = Source[buckets],
    #"Removed Columns" = Table.RemoveColumns(buckets,{"orderHint", "planId", "tasks"})
    #"Removed Columns"

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

    Source = OData.Feed("{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 ""&[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"})
    #"Reordered Columns"


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.
    Blogger Comment


Post a Comment

Note: Only a member of this blog may post a comment.