Reporting on Microsoft Planner or Microsoft Staffhub!

Reporting on Planner or StaffHub!

For a long time I have been waiting for the reporting API for Planner and Staffhub. Today I got tired of waiting and found another way to do it.

I manage to create a PowerBI report based on all the planner plans I have access to and the same method will work for Staffhub and properly many other services such as Microsoft Tasks, Microsoft Forms, etc. 

How?

Easy, but requires some Azure, Flow and PowerBI knowledge.

These are the steps to I used:

1.       Create an Azure Blob to hold the data

2.       Create a Flow to save Planner data in the Blob

3.       Access the Blob from PowerBI

Azure – Blob!

So first, lets create a Blob.

No, not that kind of Blob.

Access the Azure portal - https://portal.azure.com/

Select New and search for “storage account”

Fill out the form and click create.

I kept all settings to their default

Access the new storage and locate/copy the Storage Account Name and Access Key – you will need this for connections to Flow and PowerBI.

Next let’s create a Blob Container and call it “planner”.

That’s it, now we are ready to store data.

Hint: the Microsoft Azure Storage Explorer is a nifty tool to see and manage files in the Storage, download it here:

https://azure.microsoft.com/en-us/features/storage-explorer/

Flow ->

Go to Flow and create a new blank Flow: https://emea.flow.microsoft.com/

Configure the following steps and attributes. It is pretty straight forward and I have added notes in the Flow to explain how it is created. The trick is the last step where data is saved to Azure, everything else is pretty standard.

Note: Remember the New Line/base64 expression when the variables are populated. if this expression is not inserted, PowerBI will not understand the file content and import all the data as one line.
That's it - Save and Run the Flow.

PowerBI :-)

Now to the fun stuff – Let's load the data into PowerBI.

Create a new connection and chose Azure Blob Storage.

Type in the storage account that you copied earlier and select the files that Flow have saved.

Load the data.

Now we have the Planner data in PowerBI and with some M skills we can create cool reports.

Other thoughts...
Even though we can extract data, Planner does not have the same rules as other planning tools - this makes it more difficult to create a good portfolio overview. Ex. Due date and start date are not mandatory so you might have a lot of tasks which that you do not know when is starting or ending.
Flow is limited to only extract certain data - one thing I really miss is the possibility to extract the assigned resources. it might be possible through the "Body" element of the task (which is base64 encoded.


Technical data:
PowerBI formula:

Plans:
let
    Source = AzureStorage.Blobs("projectonlinefudev"),
    planner1 = Source{[Name="planner"]}[Data],
    #"https://projectonlinefudev blob core windows net/planner/_plans" = planner1{[#"Folder Path"="https://projectonlinefudev.blob.core.windows.net/planner/",Name="plans"]}[Content],
    #"Imported CSV" = Csv.Document(#"https://projectonlinefudev blob core windows net/planner/_plans",[Delimiter=";", Columns=13, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(#"Imported CSV",{{"Column1", type text}, {"Column2", type text}, {"Column3", type datetime}, {"Column4", type datetime}, {"Column5", Int64.Type}, {"Column6", type logical}, {"Column7", type datetime}, {"Column8", type text}, {"Column9", type datetime}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Task Id"}, {"Column2", "Task Title"}, {"Column3", "Start date"}, {"Column4", "Due date"}, {"Column5", "Percent complete"}, {"Column6", "Has Description"}, {"Column7", "Created date"}, {"Column8", "Created By"}, {"Column9", "Completed date"}, {"Column10", "Plan Id"}, {"Column11", "Plan Title"}, {"Column12", "Bucket Id"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"Bucket Id"},Buckets,{"Column1"},"Buckets",JoinKind.LeftOuter),
    #"Expanded Buckets" = Table.ExpandTableColumn(#"Merged Queries", "Buckets", {"Column3", "Phase"}, {"Buckets.Column3", "Buckets.Phase"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Buckets",{{"Buckets.Column3", "Bucket"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Task Id", "Bucket Id", "Column13"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Duration", each if Number.From([Due date]-[Created date]) <= 0 or [Due date] = null or [Created date] = null  then 1 else Number.From([Due date]-[Created date])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Duration", type number}})
in
    #"Changed Type1"

Buckets:
let
    Source = AzureStorage.Blobs("projectonlinefudev"),
    planner1 = Source{[Name="planner"]}[Data],
    #"https://projectonlinefudev blob core windows net/planner/_buckets" = planner1{[#"Folder Path"="https://projectonlinefudev.blob.core.windows.net/planner/",Name="buckets"]}[Content],
    #"Imported CSV" = Csv.Document(#"https://projectonlinefudev blob core windows net/planner/_buckets",[Delimiter=";", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(#"Imported CSV",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Lower([Column3])),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Bucket"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Phase", each if
Text.Contains([Bucket], "opgave") or
Text.Contains([Bucket], "%") or
Text.Contains([Bucket], "progress") or
[Bucket] = "started" or
Text.Contains([Bucket], "doing")
then "In Progress"

else if
Text.Contains([Bucket], "completed") or
Text.Contains([Bucket], "done") or
Text.Contains([Bucket], "finished") or
Text.Contains([Bucket], "solved")  or
Text.Contains([Bucket], "fã¦rdig") or
Text.Contains([Bucket], "klar til test")
then "Completed"
else "Not Started")
in
    #"Added Custom1"

Flow:
{"$schema":"https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#","contentVersion":"1.0.0.0","parameters":{"logicAppName":{"type":"String","metadata":{"description":"Name of the logic app."}},"logicAppLocation":{"defaultValue":"[resourceGroup().location]","allowedValues":["eastasia","southeastasia","centralus","eastus","eastus2","westus","northcentralus","southcentralus","northeurope","westeurope","japanwest","japaneast","brazilsouth","australiaeast","australiasoutheast","southindia","centralindia","westindia","canadacentral","canadaeast","westcentralus","westus2","[resourceGroup().location]"],"type":"String","metadata":{"description":"Location of the logic app."}},"planner_Connection_Name":{"defaultValue":"planner","type":"String","metadata":{"description":"Name of the connection."}},"azureblob_Connection_Name":{"defaultValue":"azureblob","type":"String","metadata":{"description":"Name of the connection."}}},"resources":[{"type":"Microsoft.Logic/workflows","name":"[parameters('logicAppName')]","apiVersion":"2016-06-01","location":"[parameters('logicAppLocation')]","properties":{"state":"Disabled","definition":{"$schema":"https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#","contentVersion":"1.0.0.0","parameters":{"$connections":{"defaultValue":{},"type":"Object"},"$authentication":{"defaultValue":{},"type":"SecureObject"}},"triggers":{"Recurrence":{"recurrence":{"frequency":"Hour","interval":1},"type":"Recurrence","description":"Set the recurrency of the Flow, you can also play around with other triggers - ex. Plan Created."}},"actions":{"List_my_plans":{"runAfter":{},"metadata":{"flowSystemMetadata":{"swaggerOperationId":"ListMyPlans_V2"}},"type":"ApiConnection","inputs":{"host":{"connection":{"name":"@parameters('$connections')['planner']['connectionId']"}},"method":"get","path":"/v1.0/me/planner/plans","authentication":"@parameters('$authentication')"}},"Apply_to_each":{"foreach":"@body('List_my_plans')?['value']","actions":{"List_tasks":{"runAfter":{},"metadata":{"flowSystemMetadata":{"swaggerOperationId":"ListTasks_V2"}},"type":"ApiConnection","inputs":{"host":{"connection":{"name":"@parameters('$connections')['planner']['connectionId']"}},"method":"get","path":"/v1.0/planner/plans/@{encodeURIComponent(items('Apply_to_each')?['id'])}/tasks","authentication":"@parameters('$authentication')"},"description":"List all the task for the current plan. Note the UI could not find the Id at first, but after typing \"Id\" in the field and then searching for Id it appeared."},"Apply_to_each_2":{"foreach":"@body('List_tasks')?['value']","actions":{"Get_task_details":{"runAfter":{},"metadata":{"flowSystemMetadata":{"swaggerOperationId":"GetTaskDetails_V2"}},"type":"ApiConnection","inputs":{"host":{"connection":{"name":"@parameters('$connections')['planner']['connectionId']"}},"method":"get","path":"/v1.0/planner/tasks/@{encodeURIComponent(items('Apply_to_each_2')?['id'])}/details","authentication":"@parameters('$authentication')"}},"Append_to_string_variable_2":{"runAfter":{"Get_task_details":["Succeeded"]},"type":"AppendToStringVariable","inputs":{"name":"plans","value":"@{items('Apply_to_each_2')?['id']};@{items('Apply_to_each_2')?['title']};@{items('Apply_to_each_2')?['startDateTime']};@{items('Apply_to_each_2')?['dueDateTime']};@{items('Apply_to_each_2')?['percentComplete']};@{items('Apply_to_each_2')?['hasDescription']};@{items('Apply_to_each_2')?['createdDateTime']};@{items('Apply_to_each_2')?['createdBy']?['user']?['displayName']};@{items('Apply_to_each_2')?['completedDateTime']};@{items('Apply_to_each')?['id']};@{items('Apply_to_each')?['title']};@{items('Apply_to_each_2')?['bucketId']};@{base64ToString()}"},"description":"Saving task + plan data to a variable. Notice the \"base64ToString('DQo=')\" in the end. This is an Expression under the Conversion category that adds a new line. It is needed for PowerBI to understand the where a new row starts."}},"runAfter":{"List_tasks":["Succeeded"]},"type":"Foreach"},"List_buckets":{"runAfter":{"Apply_to_each_2":["Succeeded"]},"metadata":{"flowSystemMetadata":{"swaggerOperationId":"ListBuckets_V2"}},"type":"ApiConnection","inputs":{"host":{"connection":{"name":"@parameters('$connections')['planner']['connectionId']"}},"method":"get","path":"/v1.0/planner/plans/@{encodeURIComponent(items('Apply_to_each')?['id'])}/buckets","authentication":"@parameters('$authentication')"},"description":"Buckets are the headings above the tasks. ex. To-Do, In Progress, Completed, ..."},"Apply_to_each_3":{"foreach":"@body('List_buckets')?['value']","actions":{"Append_to_string_variable":{"runAfter":{},"type":"AppendToStringVariable","inputs":{"name":"buckets","value":"@{items('Apply_to_each_3')?['id']};@{items('Apply_to_each_3')?['planId']};@{items('Apply_to_each_3')?['name']};@{base64ToString(base64ToString())}"},"description":"Again, remember to add the New Line character."}},"runAfter":{"List_buckets":["Succeeded"]},"type":"Foreach"}},"runAfter":{"Initialize_variable_2":["Succeeded"]},"type":"Foreach","description":"For each plan we need to load all tasks and buckets. \"Value\" is the plan."},"Initialize_variable":{"runAfter":{"List_my_plans":["Succeeded"]},"type":"InitializeVariable","inputs":{"variables":[{"name":"plans","type":"String"}]},"description":"For faster and easier text handling i use a variable to store plan and task information in."},"Create_blob":{"runAfter":{"Apply_to_each":["Succeeded"]},"metadata":{"flowSystemMetadata":{"swaggerOperationId":"CreateFile"}},"type":"ApiConnection","inputs":{"host":{"connection":{"name":"@parameters('$connections')['azureblob']['connectionId']"}},"method":"post","path":"/datasets/default/files","queries":{"folderPath":"/planner","name":"plans"},"body":"@variables('plans')","authentication":"@parameters('$authentication')"},"description":"Lastly, save the plan variable to a file in Azure Blub.","runtimeConfiguration":{"contentTransfer":{"transferMode":"Chunked"}}},"Initialize_variable_2":{"runAfter":{"Initialize_variable":["Succeeded"]},"type":"InitializeVariable","inputs":{"variables":[{"name":"buckets","type":"String"}]},"description":"I also have a variable for the Buckets"},"Create_blob_2":{"runAfter":{"Create_blob":["Succeeded"]},"metadata":{"flowSystemMetadata":{"swaggerOperationId":"CreateFile"}},"type":"ApiConnection","inputs":{"host":{"connection":{"name":"@parameters('$connections')['azureblob']['connectionId']"}},"method":"post","path":"/datasets/default/files","queries":{"folderPath":"/planner","name":"buckets"},"body":"@variables('buckets')","authentication":"@parameters('$authentication')"},"description":"And save the buckets to a different file.","runtimeConfiguration":{"contentTransfer":{"transferMode":"Chunked"}}}},"outputs":{}},"parameters":{"$connections":{"value":{"planner":{"id":"[concat('/subscriptions/', subscription().subscriptionId, '/providers/Microsoft.Web/locations/', parameters('logicAppLocation'), '/managedApis/', 'planner')]","connectionId":"[resourceId('Microsoft.Web/connections', parameters('planner_Connection_Name'))]","connectionName":"[parameters('planner_Connection_Name')]"},"azureblob":{"id":"[concat('/subscriptions/', subscription().subscriptionId, '/providers/Microsoft.Web/locations/', parameters('logicAppLocation'), '/managedApis/', 'azureblob')]","connectionId":"[resourceId('Microsoft.Web/connections', parameters('azureblob_Connection_Name'))]","connectionName":"[parameters('azureblob_Connection_Name')]"}}}}},"dependsOn":["[resourceId('Microsoft.Web/connections', parameters('planner_Connection_Name'))]","[resourceId('Microsoft.Web/connections', parameters('azureblob_Connection_Name'))]"]},{"type":"Microsoft.Web/connections","name":"[parameters('planner_Connection_Name')]","apiVersion":"2016-06-01","location":"[parameters('logicAppLocation')]","properties":{"api":{"id":"[concat('/subscriptions/', subscription().subscriptionId, '/providers/Microsoft.Web/locations/', parameters('logicAppLocation'), '/managedApis/', 'planner')]"},"displayName":"[parameters('planner_Connection_Name')]"}},{"type":"Microsoft.Web/connections","name":"[parameters('azureblob_Connection_Name')]","apiVersion":"2016-06-01","location":"[parameters('logicAppLocation')]","properties":{"api":{"id":"[concat('/subscriptions/', subscription().subscriptionId, '/providers/Microsoft.Web/locations/', parameters('logicAppLocation'), '/managedApis/', 'azureblob')]"},"displayName":"[parameters('azureblob_Connection_Name')]"}}]}