2026 Update: Unveiling The Secrets Of Data Modelling With SharePoint, Power Apps & Power Automate

The original 2023 session delivered by Paul Stork provided a foundational masterclass in mapping natural-language requirements into functional entity-relationship (ER) models. The session demonstrated extracting entities, establishing SharePoint list relationships, overcoming delegation limits in Power Apps, and automating workflows. The core concepts of relational data remain unchanged, but the execution methodologies have fundamentally evolved as of April 2026.1

Driven by the Microsoft Power Platform 2026 Release Wave 1 and the pervasive integration of artificial intelligence, manual configuration has largely been replaced by prompt-driven architecture. Developers now orchestrate data models using natural language, bypass legacy limitations using advanced caching, and manage complex integrations within modernized interfaces.

This comprehensive companion guide translates the manual builds from the 2023 session into modern April 2026 execution paths. The report provides exhaustive, step-by-step instructions for rebuilding the original scenarios using current tools, updated governance standards, and the latest Copilot capabilities.

What's Changed Since This Session

The foundational concepts of relational data modeling remain intact, but the execution layer has shifted drastically toward AI-assisted generation and enterprise-scale alternatives. The following table provides a strict, factual summary of the core technologies demonstrated in the original session and their status as of April 2026.5

How to Build This Today

The following sections systematically deconstruct each scenario demonstrated in the original session. Each segment restates the original build, identifies the modern paradigm, and provides exhaustive, step-by-step instructions for reconstructing the solution using the April 2026 toolset.

Scenario 1: Extracting Entities & Creating the ER Diagram

The Original Session Build:

The session demonstrated reading a natural-language project requirements document outlining a project management tool. The presenter manually extracted entities like Projects, Tasks, and Clients. The presenter then identified their properties, mapped relationships, and drew a conceptual Entity Relationship (ER) diagram using a modified UML approach.

The April 2026 Modern Approach: The manual extraction of entities and the physical drawing of UML diagrams is now handled autonomously by Copilot in Power Apps. Developers simply paste the requirements document into the Copilot chat interface.

The generative AI model instantly parses the unstructured text and outputs a functional, proposed data model. The "Start with data" workspace translates unstructured text into relational tables instantly, displaying a visual representation of the schema. Developers refine this schema conversationally before any underlying lists or tables are provisioned.

The system understands natural language relationship definitions. Developers can dictate one-to-many or many-to-many relationships simply by describing the business logic.

Step-by-Step April 2026 Instructions:

1. Navigate to the Power Apps maker portal: https://make.powerapps.com.
2. Ensure the active environment has a Microsoft Dataverse database provisioned, as this is required for the Copilot workspace.
3. Ensure the active user possesses the System Customizer security role.
4. On the primary home screen, locate and select the Start with data option.
5. Select Create new data to initialize the table editor and open the Copilot panel.
6. In the Copilot text box situated on the right side of the screen, paste the exact natural-language requirements document from the project scope.
7. Press Enter or select the Submit icon.
8. Copilot interprets the text, extracts the entities, and generates the proposed tables (e.g., Projects, Tasks, Clients).
9. Review the generated schema within the Create new tables workspace.
10. The workspace displays the columns, proposed data types, and primary keys for each entity.
11. Refine the relationships conversationally by entering prompts directly into the chat.
12. For example, instruct Copilot: "Create a one-to-many relationship linking the Projects table to the Tasks table".1
13. Instruct Copilot to generate specific columns: "Add a Choice column to the Tasks table to track Status, with options for Not Started, In Progress, and Completed".1
14. Select Save and exit to finalize the schema and provision the backend data structure.
15. Alternatively, select Save and open app to instruct Power Apps to immediately generate a fully functional canvas app based on this new data model.

Quick Win: To achieve the fastest possible start, paste the entire project requirements document into the Copilot chat and append a specific architectural command. Use the phrase: "Generate tables for this scope and automatically include lookup columns to establish 1:N relationships between all parent and child entities." This generates a comprehensive schema in a single prompt.

Warning: Generating tables via the Power Apps Copilot defaults to creating Microsoft Dataverse tables, not SharePoint lists. If the organizational architecture strictly mandates SharePoint lists, the developer must utilize the Microsoft 365 Copilot SharePoint List Agent directly within the SharePoint interface to generate the lists.

For official documentation regarding conversational app creation, refer to: https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/ai-conversations-create-app.

Scenario 2: Mapping ER to SharePoint Lists & Managing Limits

The Original Session Build:

The session detailed the manual creation of SharePoint lists to mirror the ER model. This involved creating a parent list for Projects and a child list for Tasks. The presenter configured a lookup column in the Tasks list pointing to the Projects list to enforce a one-to-many relationship. The session heavily emphasized the 12-lookup column view threshold and discussed normalization strategies, such as using plain text IDs instead of lookup columns to avoid breaking the list view.

The April 2026 Modern Approach: SharePoint list creation can now be accelerated using the Copilot SharePoint agent to generate columns via natural language. However, the fundamental architectural limitations of SharePoint remain completely unchanged in 2026.7

SharePoint Online still enforces a strict threshold of exactly 12 lookup columns per list view. This limit is enforced because each lookup column requires a complex SQL table join on the backend database. Exceeding this limit causes SQL queries to fail, resulting in list view errors and severely degraded data retrieval performance.

Furthermore, developers must understand that standard lookups, single-value Managed Metadata, multiple-value Managed Metadata, and all Person/Group columns count equally toward this 12-column threshold. When a solution outgrows these limits, modern best practices dictate migrating to Microsoft Dataverse rather than implementing brittle workarounds in SharePoint.

Step-by-Step April 2026 Instructions:

1. Navigate to the desired SharePoint Site where the data will reside.
2. From the site homepage, navigate via the menu path: Site > New > List.
3. Select the Blank list option to manually configure the schema, bypassing templates.
4. Name the parent list "Projects" and select the Create button.
5. Once the list is provisioned, select the Settings (Gear Icon) in the top right corner.
6. Navigate to List Settings > Create Column.
7. Add the necessary text, number, and choice columns required for the Projects entity.
8. Return to the site contents page and repeat the creation process to provision a second list named "Tasks".
9. Inside the Tasks list, navigate again to List Settings > Create Column.
10. Name this new column "ProjectName" and specify the data type as Lookup (information already on this site).
11. In the detailed column configuration menu, locate the Get information from dropdown and select the "Projects" list.
12. In the In this column dropdown, select "Title" (or the appropriate primary identifier from the Projects list).
13. Scroll down to locate the Relationship settings block.
14. Check the box labeled Enforce relationship behavior.
15. Select the Restrict delete radio button. This critical setting prevents a Project from being deleted if it currently has active Tasks associated with it.
16. Alternatively, select Cascade delete if the business logic dictates that deleting a Project should automatically purge all related Tasks.
17. Select OK to establish the relational link between the two lists.

Quick Win: To proactively manage the 12-lookup limit, avoid using Person/Group columns for tracking historical users. Instead, use a simple Power Automate flow to write the user's Display Name into a standard Text column upon item creation. This preserves lookup capacity for actual table relationships.

Warning: Displaying 12 or more complex columns in a single list view will trigger an immediate threshold error, breaking the application connected to it. Administrators can no longer easily override this via the UI in SharePoint Online. If the ER model requires extensive interconnected relationships, SharePoint is fundamentally the wrong architectural choice. The project must be migrated to Microsoft Dataverse.

For official documentation on creating list relationships via lookup columns, visit: https://support.microsoft.com/en-us/office/create-list-relationships-by-using-lookup-columns-80a3e0a6-8016-41fb-ad09-8bf16d490632.

For official guidance on the 12-lookup threshold limits, visit: https://support.microsoft.com/en-au/office/working-with-list-forms-with-large-number-of-lookup-columns-cd6b459c-be4c-481f-8cf0-b1f4f67ff579.

Scenario 3: Integrating Data into Power Apps (Handling Delegation)

The Original Session Build:

The session demonstrated integrating the newly created SharePoint lists into a Power Apps canvas app to build a functional task management interface. The primary focus was configuring vertical galleries with delegable filters and sorting mechanisms. The presenter emphasized the critical importance of avoiding delegation warnings (represented by a yellow triangle icon) when querying lookup or choice columns across large datasets.

The April 2026 Modern Approach: Canvas apps now feature a modernized, refreshed user interface enforced natively via the Fluent 2 design system. Building the interface layout is dramatically faster using the Copilot panel to generate screens and rename controls.

However, the core concept of delegation remains a fundamental hurdle that developers must actively manage. Delegation refers to Power Apps pushing computational data processing to the backend data source (SharePoint) rather than downloading all raw records and processing them locally within the device's memory. If a query cannot be delegated, Power Apps will only retrieve a maximum of 500 to 2000 records, potentially resulting in inaccurate data representation.

Power Apps in 2026 still cannot fully delegate complex column types like Choice or Lookup when using the Choices() function, the Search() function, or complex operators like <> (not equal). The developer must utilize specific Power Fx techniques to filter galleries without triggering delegation warnings. A highly effective modern strategy involves caching the data locally upon app initialization.

Step-by-Step April 2026 Instructions:

1. Navigate to the Power Apps maker portal and open the target canvas app in edit mode.
2. Select the Data cylinder icon from the left navigation pane.
3. Click Add data, search for the SharePoint connector, and authenticate.
4. Connect to the specific SharePoint site and select the "Tasks" list to add it as a data source.
5. Insert a Gallery onto the screen by navigating via the top menu path: Insert > Layout > Vertical gallery.
6. Bind the gallery to the data source by setting its Items property to Tasks.
7. To filter the gallery by a Choice column (e.g., a "Status" column) without triggering a delegation warning, avoid using non-delegable operators.
8. The operator <> (not equal) cannot be delegated to SharePoint for Choice fields.
9. Instead, write a delegable Power Fx formula using the strict = operator. Configure the gallery's Items property as follows: Filter(Tasks, Status.Value = "In Progress").
10. To handle complex dynamic sorting and advanced filtering on choice columns, implement a local collection strategy.
11. Select the App object from the Tree view pane on the left.
12. In the OnStart property of the App object, create a local collection using the ClearCollect() function.
13. Enter the formula: ClearCollect(colTasks, Tasks). This caches the list in memory when the app loads.
14. Bind the gallery's Items property directly to this new collection.
15. Because the data is now in local memory, delegation limits no longer apply. Use complex formulas safely: SortByColumns(Filter(colTasks, Status.Value = Dropdown1.Selected.Value), "Created", SortOrder.Descending).

Quick Win: Collections created via ClearCollect operate entirely in the local device's memory. Loading a SharePoint list into a collection upon app start allows for infinitely complex filtering, sorting, and searching without any yellow triangle warnings.

Warning: The ClearCollect local caching strategy is only viable if the total number of items in the SharePoint list is safely under the 2,000-record local retrieval limit. If the dataset exceeds 2,000 rows, this caching strategy will fail to retrieve the entire dataset, and the developer must rely strictly on fully delegable Power Fx queries.

For official documentation regarding Power Apps delegation limits and supported functions for SharePoint, visit: https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/delegation-overview.

For documentation specifically addressing the limitations of the Choices function, visit: https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-choices.

Scenario 4: Power Automate Data Aggregation & Status Emails

The Original Session Build:

The session detailed the construction of a Power Automate flow designed to generate automated weekly status emails. This involved querying the SharePoint lists to retrieve all active items. The flow utilized nested Apply to each loops to systematically iterate through the records, aggregating task data per project, and eventually formatting the output into an HTML table for email distribution.

The April 2026 Modern Approach: The Power Automate cloud flow designer has been completely overhauled. The new designer features inline expanded property views, robust process mapping, and Copilot residing natively within the designer panel.

Crucially, the legacy reliance on slow, resource-heavy Apply to each loops for data aggregation is now considered poor architectural practice. Iterating through hundreds of SharePoint items one by one causes flows to run slowly and increases the risk of API throttling. Developers in 2026 utilize JSON-to-XML transformations with XPath expressions for lightning-fast, loop-free data grouping. Alternatively, they leverage AI Builder prompt actions to summarize the data organically using natural language models.

Step-by-Step April 2026 Instructions (Using AI Builder):

1. Navigate to the Power Automate portal: https://make.powerautomate.com.
2. From the left navigation pane, select Create > Scheduled cloud flow.
3. Name the flow "Weekly Status Report" and configure the recurrence schedule to run every Friday.
4. Once in the new designer interface, click the + Add an action button.
5. Search for and select the Get items action located under the SharePoint connector.
6. Configure the action by selecting the appropriate Site Address and selecting the "Tasks" list.
7. Expand the Advanced parameters section of the action.
8. Enter an OData filter query to restrict the payload to only retrieve active tasks. For example, enter: Status eq 'In Progress'.34
9. Click + Add an action again and search for the AI Builder connector.
10. Select the action titled Create text with GPT using a prompt.
11. In the prompt configuration window, write clear natural language instructions for the AI model.
12. Example Prompt: "Summarize the following JSON project task data into a professional HTML status report. Group the tasks by the ProjectName field. Highlight any overdue items in red text.".37
13. Click into the prompt box and insert dynamic content. Pass the value array (the raw JSON output generated from the 'Get items' action) directly into the AI prompt's input field.
14. Add a final action by searching for Send an email (V2) under the Office 365 Outlook connector.
15. Map the text output generated by the AI Builder prompt directly into the email body field.

Quick Win: Do not struggle with writing complex OData syntax manually. Use the Copilot panel residing on the right side of the flow designer. Simply type: "Write an OData filter query to return items where the DueDate is less than today," and Copilot will automatically generate the correct syntax and apply it to the Get items action.

Warning: The AI Builder prompt method requires the Power Automate Premium license. If the organization relies solely on standard Microsoft 365 licensing, the developer must use the loop-free XML/XPath aggregation method. This involves using the Compose action to convert the JSON array to XML, and utilizing XPath expressions like //ProjectName to extract distinct values for grouping without incurring premium licensing costs.

For official guidance on utilizing the Get items action and managing data payloads, visit: https://learn.microsoft.com/en-us/sharepoint/dev/business-apps/power-automate/guidance/working-with-get-items-and-get-files.

For official documentation on integrating AI Builder prompts into cloud flows, visit: https://learn.microsoft.com/en-us/ai-builder/use-a-custom-prompt-in-flow.

Scenario 5: Managing Item-Level Security

The Original Session Build:

The final scenario of the session focused on planning item-level security within the SharePoint lists based on relational data. The goal was to ensure end-users only saw the specific tasks associated with their assigned projects. This required intentionally disrupting SharePoint's default permission inheritance to restrict visibility on a per-row basis.

The April 2026 Modern Approach: The mechanics of item-level security in SharePoint remain functionally identical to 2023, but the enterprise governance surrounding it has drastically improved. Organizations now heavily utilize the SharePoint Admin Agent (a specialized Copilot extension) to autonomously analyze permissions, detect broken inheritance, and enforce data governance policies across the tenant.

However, the architectural guidance from Microsoft in 2026 strongly discourages utilizing heavy item-level permissions within SharePoint lists for application development. Breaking inheritance on thousands of individual list items severely degrades list view performance, causes search anomalies, and drastically complicates administrative maintenance. If the application requires rigorous, relationship-based security, the absolute best practice is to build the backend in Microsoft Dataverse. Dataverse features native, server-enforced Role-Based Access Control (RBAC) that scales securely without degrading performance.

Step-by-Step April 2026 Instructions (Using the SharePoint Admin Agent):

1. If the developer is forced to maintain the data within SharePoint, the security configuration must be audited relentlessly.
2. Ensure the tenant has the appropriate Microsoft 365 Copilot licenses assigned to the administrator.
3. Navigate to the central SharePoint Admin Center.
4. Open the Copilot interface to access the specialized SharePoint Admin Agent capabilities.
5. Use natural language to query the current permissions state of the architecture.
6. Enter the prompt: "Analyze the Data Governance Access report for the Projects site and identify all lists currently experiencing broken permission inheritance".38
7. The agent will parse the organizational data and provide actionable recommendations for resolving permission bloat or unauthorized access.
8. To automate the application of item-level security for new records, create an automated Power Automate cloud flow.
9. Set the flow trigger to When an item is created on the Tasks list.
10. Add the Stop sharing an item or a file action to immediately break the permission inheritance on the newly created Task item.
11. Add the Grant access to an item or a folder action. Pass the assigned user's email address (retrieved from dynamic content) into the Recipients field to grant exclusive access.

Quick Win: Ensure Copilot data security by managing permissions strictly. Because default Copilot agents search all content a user has access to, poor item-level security means Copilot might inadvertently surface sensitive project data to unauthorized users. Regular audits via the SharePoint Admin Agent are critical to prevent data leakage.

Warning: The absolute supported limit of unique permissions for items in a SharePoint list or library is 50,000, but Microsoft's recommended general limit is strictly 5,000.10 Exceeding this limit will severely impact the performance of any Power Apps galleries or Power Automate queries attempting to interact with the list.

For detailed documentation on utilizing the SharePoint Admin Agent for comprehensive content governance, visit: https://learn.microsoft.com/en-us/sharepoint/content-governance-agent.

For documentation regarding software boundaries and unique permission limits, visit: https://support.microsoft.com/en-gb/office/list-view-threshold-for-large-lists-and-libraries-e2ea4d5d-ec23-4171-95c4-c7f5b5dbfd8a.

Licensing Quick Reference

The licensing landscape has evolved to accommodate the heavy integration of Copilot features and AI agents across the ecosystem. The introduction of the Microsoft 365 E7 suite in May 2026 represents the new standard for organizations intending to scale AI and govern automated agents centrally.

The following table outlines the specific licenses required to build, deploy, and govern the complete, modern solution detailed in this report.

The modern approach to data modeling and application building emphasizes defining intent rather than executing manual configuration. By utilizing conversational AI interfaces to generate complex data models, implementing local caching techniques to bypass legacy delegation limits, and leveraging AI-driven aggregation for reporting, developers can recreate the functionality demonstrated in the original 2023 session with significantly greater speed, robust security, and enterprise scalability in 2026.