The Paradigm BI Planner data model for development increment 6 uses Azure Logic Apps to retrieve data using the Logic Apps service connectors to Active Directory and Planner. This has the advantage of not needing a Key Vault, Registered App or Token and the disadvantage, compared to using Microsoft Graph, of many more Logic Apps and a slightly older Planner interface. The logic apps will be packaged as a single deployment to a resource group to simplify setup.

The model builds on the previous design and now goes down to categories and task checklist. If a task has multiple assignees a row is created for each user. Currently, only the first Category is extracted for each task, if there is one. The model looks like this:

There are a few two-way relationships here, this enables creative use of the decomposition visual to start at any point in the graph.

For this increment, the URLs from the Logics Apps are pasted into the Power BI Power Query statements. Hopefully, the next increment will automate this, the aim is to have two parameters for the data model, a Group Name and a URL. The data model is intended to be deployed separately to the reporting, which can be Power BI, Paginated reports, or Excel.

The data model starts for a single group, the group name is provided as a parameter. All data apart from category names is provided by Azure logic apps. this ensures that the model is compliant with the firewall and security considerations for data sources.
To initiate the model a list of groups is returned from an Azure Function and the group name parameter is used to filter it down to one group Id. This basic query is converted into a function so that it can be called from other queries. A list of plans for the group is retrieved by calling the group function to get the group ID and then calling the logic app to get a list of plans.

Let's have a look at this process:

let
    longUrl = "https://etc...",
    urlParts = Uri.Parts(longUrl),
    Headers = [#"Content-Type" = "application/json"],
    Url = "https://" & urlParts[Host],
    Source = Json.Document(Web.Contents(Url,
            [Headers=Headers,
            Content=Text.ToBinary(""),
            RelativePath=urlParts[Path],
            Query=urlParts[Query]])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitTextByDelimiter(","), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "groupId"}, {"Column2", "groupName"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([groupName] = pGroupName)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"groupName"})
in
    #"Removed Columns"{0}[groupId]

This query returns a single value and is then wrapped in a function fxGroupId that is called whenever a GroupId is needed like this: groupId = fxGroupId(pGroupName)

This is the general pattern for the queries. To comply with Power Query firewall requirements all data except for the initial group name, which is a parameter, must be retrieved from either the web or from the power query model, but not both. A query to and API wrapped in a function helps to achieve this goal. Some of the function queries have dummy parameters to aid development, as the approach is to test the query and then convert it into a function for use by the other queries. When the function is called the parameter supplied replaces the parameter used to develop the query. If the function needs to change, the query is updated which automatically updates the query.
This is what the Plan Tasks function currently looks like: 

Plan buckets are retrieved from a logic app using a query that references a dummy Plan Id parameter. This query is also converted into a function so that it can be called to build the Bucket table. All table entities are referenced by their GUID Ids. Plan tasks are also retrieved from a logic app using the dummy planner id. The task table includes bucket details. This query is also turned into a function.
The task detail query retrieves checklist details for each task this is also converted into a function, there is a dummy task Id parameter.
The Task Detail table is created by retrieving all the plans for the group and then using the plan is to retrieve the tasks for each plan if the task has an item count greater than 0 the task detail function is called to retrieve checklist items. Which are added as a table at the end of each task row but not expanded, this column is then hidden from the report view, it is used in the checklist query to retrieve the items. The main task table is the fact table for the data model.

The Task checklist table is created by referencing the task detail table and removing all columns except for the task ID and the checklist table column the checklist table is then expanded.

The category table is created by retrieving the category details for each plan from a logic app the categories are then merged with a default category table and a unique instance ID is created by appending the category name to the plan ID. The task detail table has a column that also contains this unique key which is the first category for any task. This is necessary because categories do not have a unique GUID identifier.
This is a big query, here are the highlights:

// Query 
    value = Source[value],
// Add the tasks
Table.AddColumn(…, "Tasks", each fxPlanTasks([planId],pGroupName)),
// Expand The Tasks
Table.ExpandTableColumn(…, "Tasks", {…}),
// Get the ChecklistItems, but don’t expand them
Table.AddColumn(…, "Custom", each if [checklistItemCount] > 0 then "function" else null),
// Make sure there is a start date
each if [startDateTime] = null then fxPreviousDate([dueDate]) else DateTime.Date([startDateTime]),
// Extract All Assignees as a list
Table.AddColumn(.., "userId", each Record.FieldNames([assignments])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"startDateTime", "dueDateTime", "assignments"}),
    #"Expanded userId" = Table.ExpandListColumn(#"Removed Columns", "userId"),
    #"Added Custom1" = Table.AddColumn(#"Expanded userId", "categoryList", each Record.FieldNames([appliedCategories])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Category", each if List.IsEmpty([categoryList]) then "" else [categoryList]{0}),
    #"Merged Columns" = Table.CombineColumns(#"Added Custom2",{"planId", "Category"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"planCategory"),
    #"Removed Columns1" = Table.RemoveColumns(#"Merged Columns",{"appliedCategories", "categoryList"}),
    #"Added Conditional Column2" = Table.AddColumn(#"Removed Columns1", "CheckList", each if [checklistItemCount] > 0 then fxCheckList([taskId]) else null)
in
     #"Added Conditional Column2"

A user table is constructed from a logic app for all users who belong to the group, this has the User GUID Id as a key

Next Steps

The next increment focuses and an improved, simpler and more efficient, API, this is being achieved using Azure API management. Also in the pipeline is the deployment project and of course a Power BI App.