This is the companion blog post to the Planner In Power BI YouTube Video. 

If you are not familiar with Planner, I suggest creating a small project and looking at it with Graph Explorer. 'All My Planner Tasks' is a simple query that returns interesting content.

Loading data from planner is challenging as there is no specific API to do this, but the MS Graph API has access to this data. This requires a logged-on user, which is natural as user permissions need to apply when reading the data. The user also needs to use an Azure Application which will have the permission to read the data, with the permission Group.Read.All. This requires that some keys need to be available, which means we need the Azure Key Vault. We can call the API using a Logic App and process the JSON data using Power Query. Security can use the Azure Managed Identity Feature to enable the Logic App to read the Key Vault. The first call to the MS Graph will return an Access Token, which we can use to retrieve the rest of the data. The token will have a life of one hour, so we need to get one without any human intervention.

We are using these components to get the graph data:

• Registered Application
• Key Vault
• Logic App to get Authorisation Token
• Power Query to get Planner Data

I used a development environment to create these components. Refer to the video for screenshots.

Registering the Application

Register an Application and create a secret. This effectively creates a service principal to access MS Graph. The ClientId and Secret of the application act in the same manner as a user name and password to access MS Graph.

You will need the TenantID, ClientID and the ClientSecret from the Application, so keep them handy.

Create a Resource Group

The same Subscription, Resource Group and Location should be used for the Key Vault, the Logic App, and any other resources you may create.

Create a Key Vault

Create a Key Vault using the same Subscription, Resource Group and Location.

Set the Access Policy to ‘Azure role-based access control’ (RBAC). RBAC is essential for using Managed Identities.

Add yourself to AccessControl(IAM). This is where you will now control all access to the vault. Chose Role ‘Key Vault Administrator’, select yourself and ‘Save’.

Create a Logic App

You want a Consumption-based Logic App 

Create the App using the same Resource Group and Region. I have used the same name that I originally used as a template that I’ll try to use. I have turned ‘Associate with integrated service environment’ off, as I have not used this feature yet.

 When the deployment is complete, go to the resource, this is an empty workflow. We’ll add some basic functionality. First, we add a Trigger “When an HTTP request is received” and a response action. 

Then we configure Managed Identity so that we can access the key vault. This is very simple, we got to Identity and turn System assigned Identity on, don’t forget to save. This will register a new identity with Active Directory.

Prepare The Key Vault

Now we got back to the Key Vault that we created and, using Access Control (IAM) add a role assignment of ‘Key Vault Secrets User’ to the Logic App ‘Planner Token’.

The purpose of using the Key Vault is so that the Logic  App can access the parameters needed to retrieve an authentication token from the MS Graph service. This token has a life of one hour and will be used to retrieve the desired planner data.

These keys need to be created:

• ClientID
• ClientSecret
• TenantID
• UserName
• UserPassword

The spelling was derived from the names used in the Postman HTTP call to get the token. The first three keys are from the registered application. I used my own User Name and Password. Any username and password that has permission to access the planner are fine. Those permissions are then delegated to the application to call MS Graph. Note that delegation cannot add permission to which a user is not entitled. Note that these keys are spelt exactly as they are consumed in the Logic App. As a Key Vault Secrets Officer, you have permission to add secrets. Use the Secrets option to add secrets.

Logic Apps have an Azure Key Vault Action, which you can use to get the keys/secrets, add the Get Secret Action to the Logic App, for the first secret, it will create the connection, and you can select ‘Connect with managed identity (preview)’ to create the connection. Type in ‘TenantID’ and save the Logic App. Then ‘Run’ it to see if it works. If that works, add an ‘initialize variable’ step afterwards and set a ‘TenantID’ variable to the secret's value. This is the field ‘value’, not ‘The secret.

Here is the code for the Logic App Actions:

{
            "Get_ClientID": {
                "inputs": {
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['keyvault']['connectionId']"
                        }
                    },
                    "method": "get",
                    "path": "/secrets/@{encodeURIComponent('ClientID')}/value"
                },
                "runAfter": {
                    "Initialize_TenantID": [
                        "Succeeded"
                    ]
                },
                "type": "ApiConnection"
            },
            "Get_ClientSecret": {
                "inputs": {
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['keyvault']['connectionId']"
                        }
                    },
                    "method": "get",
                    "path": "/secrets/@{encodeURIComponent('ClientSecret')}/value"
                },
                "runAfter": {
                    "Initialize_ClientID": [
                        "Succeeded"
                    ]
                },
                "type": "ApiConnection"
            },
            "Get_TenantID": {
                "inputs": {
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['keyvault']['connectionId']"
                        }
                    },
                    "method": "get",
                    "path": "/secrets/@{encodeURIComponent('TenantID')}/value"
                },
                "runAfter": {},
                "type": "ApiConnection"
            },
            "Get_UserName": {
                "inputs": {
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['keyvault']['connectionId']"
                        }
                    },
                    "method": "get",
                    "path": "/secrets/@{encodeURIComponent('UserName')}/value"
                },
                "runAfter": {
                    "Initialize_ClientSecret": [
                        "Succeeded"
                    ]
                },
                "type": "ApiConnection"
            },
            "Get_UserPassword": {
                "inputs": {
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['keyvault']['connectionId']"
                        }
                    },
                    "method": "get",
                    "path": "/secrets/@{encodeURIComponent('UserPassword')}/value"
                },
                "runAfter": {
                    "Initialize_UserName": [
                        "Succeeded"
                    ]
                },
                "type": "ApiConnection"
            },
            "HTTP": {
                "inputs": {
                    "body": "grant_type=password&client_id=@{variables('ClientID')}&client_secret=@{variables('ClientSecret')}&scope=https%3A%2F%2Fgraph.microsoft.com%2F.default&userName=@{variables('UserName')}&password=@{variables('UserPassword')}",
                    "headers": {
                        "Accept": "*/*",
                        "Accept-Encoding": "gzip, deflate, br",
                        "Cache-Control": "no-cache",
                        "Connection": "keep-alive",
                        "Content-Length:": "128",
                        "Content-Type": "application/x-www-form-urlencoded",
                        "Host": "login.microsoftonline.com"
                    },
                    "method": "POST",
                    "uri": "@variables('EndPointURL')"
                },
                "runAfter": {
                    "Initialize_EndPointURL": [
                        "Succeeded"
                    ]
                },
                "type": "Http"
            },
            "Initialize_ClientID": {
                "inputs": {
                    "variables": [
                        {
                            "name": "ClientID",
                            "type": "string",
                            "value": "@body('Get_ClientID')?['value']"
                        }
                    ]
                },
                "runAfter": {
                    "Get_ClientID": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "Initialize_ClientSecret": {
                "inputs": {
                    "variables": [
                        {
                            "name": "ClientSecret",
                            "type": "string",
                            "value": "@body('Get_ClientSecret')?['value']"
                        }
                    ]
                },
                "runAfter": {
                    "Get_ClientSecret": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "Initialize_EndPointURL": {
                "inputs": {
                    "variables": [
                        {
                            "name": "EndPointURL",
                            "type": "string",
                            "value": "@{concat('https://login.microsoftonline.com/',string(variables('TenantId')),'/oauth2/v2.0/token')}"
                        }
                    ]
                },
                "runAfter": {
                    "Initialize_UserPassword": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "Initialize_TenantID": {
                "inputs": {
                    "variables": [
                        {
                            "name": "TenantID",
                            "type": "string",
                            "value": "@body('Get_TenantID')?['value']"
                        }
                    ]
                },
                "runAfter": {
                    "Get_TenantID": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "Initialize_UserName": {
                "inputs": {
                    "variables": [
                        {
                            "name": "UserName",
                            "type": "string",
                            "value": "@body('Get_UserName')?['value']"
                        }
                    ]
                },
                "runAfter": {
                    "Get_UserName": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "Initialize_UserPassword": {
                "inputs": {
                    "variables": [
                        {
                            "name": "UserPassword",
                            "type": "string",
                            "value": "@body('Get_UserPassword')?['value']"
                        }
                    ]
                },
                "runAfter": {
                    "Get_UserPassword": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "Response": {
                "inputs": {
                    "body": "@body('HTTP')",
                    "statusCode": 200
                },
                "kind": "Http",
                "runAfter": {
                    "HTTP": [
                        "Succeeded"
                    ]
                },
                "type": "Response"
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "parameters": {
            "$connections": {
                "defaultValue": {},
                "type": "Object"
            }
        },
        "triggers": {
            "manual": {
                "inputs": {
                    "schema": {}
                },
                "kind": "Http",
                "type": "Request"
            }
        }
    }

Add the connection to the Key Vault first, and then either modify the code or add the rest manually. The actions are in alphabetic order in the code. Pay attention to the 'runAfter'.

Test the Logic App by Giving it a run. If it all works, it's ready to use, so take a copy of the URL of the request.

Now we are ready to create a dataflow, although you can test on desktop PowerBI.

First is the function to get the token, called 'GetToken':

let
  Source = () => let
  Url = {YourURL},
  Source = Json.Document(Web.Contents(Url, [Content=Text.ToBinary("")])),
  #"Converted to Table" = Record.ToTable(Source),
  #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each [Name] = "access_token"),
  #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows", {"Name"}),
  Token = List.First(#"Removed Columns"[Value])
in
  Token
in
  Source

The URL must be hardcoded to avoided refresh issues. PowerBI doesn't like dynamic URLs.

Once you have tested the token, delete any test query such as 'Invoked Function'. Also, the Token marked as 'Not Loaded'.

These queries are designed to retrieve a single plan, get the PlanId of the plan you want to report on from the Graph Explorer.

Add a parameter 'StartDate'. This is used in the queries to replace null start dates.

Here is the code to get a Plan:

let
  Url = "https://graph.microsoft.com/beta/groups/{PlanId}/plans",
  Source = Json.Document(Web.Contents(Url, [Headers=[Authorization=GetToken()]])),
  PlanList = Source[value],
  PlanTable = Table.FromList(PlanList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  ExpandRecord = Table.ExpandRecordColumn(PlanTable, "Column1", {"owner", "title", "id"}, {"owner", "title", "id"}),
  #"Renamed Columns" = Table.RenameColumns(ExpandRecord, {{"owner", "groupId"}, {"id", "planId"}, {"title", "plan"}}),
  #"Transform columns" = Table.TransformColumnTypes(#"Renamed Columns", {{"groupId", type text}, {"plan", type text}, {"planId", type text}}),
  #"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"groupId", null}, {"plan", null}, {"planId", null}})
in
  #"Replace errors"

Put your desired PlanId into the URL, you can test the URL in the Graph Explorer

When you have that done. get the Buckets:

let
  BucketUrl = "https://graph.microsoft.com/beta/planner/plans/{PlanId}/buckets",
  Source = Json.Document(Web.Contents(BucketUrl, [Headers=[Authorization=GetToken()]])),
  BucketList = Source[value],
  BucketTable = Table.FromList(BucketList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  ExpandRecord = Table.ExpandRecordColumn(BucketTable, "Column1", {"name", "planId", "id"}, {"name", "planId", "id"}),
  #"Renamed Columns" = Table.RenameColumns(ExpandRecord, {{"id", "bucketId"}, {"name", "bucketName"}}),
  #"Transform columns" = Table.TransformColumnTypes(#"Renamed Columns", {{"bucketName", type text}, {"planId", type text}, {"bucketId", type text}}),
  #"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"bucketName", null}, {"planId", null}, {"bucketId", null}})
in
  #"Replace errors"

Tasks:

let
  PlanUrl = "https://graph.microsoft.com/beta/planner/plans/{PlanId}/tasks",
  Source = Json.Document(Web.Contents(PlanUrl, [Headers=[Authorization=GetToken()]])),
  TaskList = Source[value],
  TaskTable = Table.FromList(TaskList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  ExpandRecord = Table.ExpandRecordColumn(TaskTable, "Column1", {"planId", "bucketId", "title", "percentComplete", "startDateTime", "dueDateTime", "completedDateTime", "id", "assignments"}, {"planId", "bucketId", "title", "percentComplete", "startDateTime", "dueDateTime", "completedDateTime", "id", "assignments"}),
  #"Renamed Columns" = Table.RenameColumns(ExpandRecord, {{"id", "taskId"}}),
  #"Added Custom" = Table.AddColumn(#"Renamed Columns", "DueDate", each DateTime.Date(DateTime.FromText([dueDateTime]))),
  #"Added Custom1" = Table.AddColumn(#"Added Custom", "StartDate", each DateTime.Date(DateTime.FromText([startDateTime]))),
  #"Removed Columns" = Table.RemoveColumns(#"Added Custom1", {"dueDateTime", "completedDateTime", "startDateTime", "assignments", "planId"}),
  #"Replaced Value" = Table.ReplaceValue(#"Removed Columns", null, StartDate, Replacer.ReplaceValue, {"StartDate"}),
  #"Transform columns" = Table.TransformColumnTypes(#"Replaced Value", {{"bucketId", type text}, {"title", type text}, {"percentComplete", type text}, {"taskId", type text}, {"DueDate", type date}, {"StartDate", type date}}),
  #"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"bucketId", null}, {"title", null}, {"percentComplete", null}, {"taskId", null}, {"DueDate", null}, {"StartDate", null}})
in
  #"Replace errors"

And Task Assignments: