cancel
Showing results for 
Search instead for 
Did you mean: 
BCLS776

Structuring Your Power Apps Data for Success

Power Apps appeals to many users due to its low-code features, ease of connecting to data, and Excel-like functionality. While this makes the learning curve for Power Apps less steep than for a traditional coding environment, some habits don’t carry over very well from the Excel world.

 

First, two rules to remember about making data and tables work easier for app makers:

  1. Put only one kind of “thing” in each table, and then include just enough other columns needed to describe that thing
  2. Organize the same kind of data vertically in the same column in the table

For example, let’s say you are building an app that handles orders from your online store. You’ll probably want to keep track of customers, delivery addresses, orders, products, and inventory. However tempting it seems, avoid trying to do this all within one big table. Big tables are slow & tough to manage, and even tougher to query properly in the future. Imagine trying to tell your app how to query a big table for only customers when it also includes all your product information – you end up querying on top of queries to get the answer you wanted.

 

Instead, consider separate tables for all those “things” (customers, delivery addresses, orders, products, and inventory) and link the tables together through common IDs. In your app, you’ll be able to use the LookUp() function to pick out the values you need from a second table, based on information contained in another table.

 

The next trap that newer app makers fall into is trying to add a column when they should only add a row. This goes against Rule #2 above. For example, a user might think that when they have a monthly, recurring order for their online store, then they should add a column to their order table for every month of the order. That might work OK when using Excel, but in a Power App, it becomes difficult to handle in a programmatic way. How do you tell Power Apps which columns to look in for values, for any given order? In addition, this data style becomes very difficult to maintain because new month columns get added continually, leading to an ever-growing table.

 

Instead of a monstrous & difficult-to-maintain table, simply add rows. There is nothing wrong with having more than one order (row) in your Orders table for a recurring order and having different order dates listed with each one. If your app needs a summary of orders for that customer or product, the Filter() function will help you assemble only those rows that matter to that question. In this way, order dates all remain in one column, instead of spread throughout multiple columns, and your data stays organized.

 

I hope those two rules help you get started down the right path with your app.

Bryan

Comments

Nice one. Wanted to add if using Dataverse as a backend (which is the MS recommendation for canvas apps)  have a look at tip #5 from: https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/Top-15-best-practices-when-configuring...