Adventures with Power Platform: Sample Timesheet Canvas App

I wanted to create a Canvas App that took a list of Time Transactions and showed them in a matrix format by Task and Duration.

I also wanted to edit the data. ✏️

If you want to see it in action in your Environment, download it at https://mattruma.com/wp-content/uploads/2024/09/TimesheetSolution_1_0_0_0.zip.

Let’s breakdown what I have in my OnStart event in my Canvas App.

I set gblStartDate to the date of first day of the current week, and then I set gblEndDate to the date of the end of the current week. 📅

12Set(gblStartDate, Today() – Weekday(Today(), StartOfWeek.Monday) + 1);Set(gblEndDate, DateAdd(gblStartDate, 7, TimeUnit.Days));

I create da dummy set of data, I am just using a Collection, but this is easily be replaced with a SharePoint or Dataverse data source. 🔨

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566ClearCollect(    colTimeTransactions,    {        Id: GUID(),        User: “Malcolm Reynolds”,        Date: DateAdd(gblStartDate, 1, TimeUnit.Days),        Task: “Negotiate cargo deal”,        Duration: 4    },    {        Id: GUID(),        User: “Malcolm Reynolds”,        Date: DateAdd(gblStartDate, 1, TimeUnit.Days),        Task: “Reprimand Jayne for questionable behavior”,        Duration: 2    },    {        Id: GUID(),        User: “Malcolm Reynolds”,        Date: DateAdd(gblStartDate, 1, TimeUnit.Days),        Task: “Reprimand Jayne for questionable behavior”,        Duration: 4    },    {        Id: GUID(),        User: “Malcolm Reynolds”,        Date: DateAdd(gblStartDate, 2, TimeUnit.Days),        Task: “Reprimand Jayne for questionable behavior”,        Duration: 2.5    },    {        Id: GUID(),        User: “Malcolm Reynolds”,        Date: DateAdd(gblStartDate, 3, TimeUnit.Days),        Task: “Lead a heist or smuggling operation”,        Duration: 2    },    {        Id: GUID(),        User: “Malcolm Reynolds”,        Date: DateAdd(gblStartDate, 4, TimeUnit.Days),        Task: “Plan a risky mission to retrieve stolen cargo”,        Duration: 3    },    {        Id: GUID(),        User: “Malcolm Reynolds”,        Date: DateAdd(gblStartDate, 5, TimeUnit.Days),        Task: “Plan a risky mission to retrieve stolen cargo”,        Duration: 3    },    {        Id: GUID(),        User: “Malcolm Reynolds”,        Date: DateAdd(gblStartDate, 5, TimeUnit.Days),        Task: “Check in on the crew to ensure everyone’s pulling their weight”,        Duration: 4.5    },    {        Id: GUID(),        User: “Malcolm Reynolds”,        Date: DateAdd(gblStartDate, 5, TimeUnit.Days),        Task: “Fix a broken part on Serenity’s engine with Kaylee”,        Duration: 1.5    });

I then created a Collection of distinct Tasks, based on the Task for each Time Transaction.

1234567ClearCollect(    colTasks,    Distinct(        colTimeTransactions,        Task    ));

And finally, I built the Matrix based on the Collection of Tasks.

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061Clear(colTimeTransactionsMatrix);ForAll(    colTasks,    Collect(        colTimeTransactionsMatrix,        {            Id: GUID(),            Task: Value,            Mon: Coalesce(                Sum(                    Filter(                        colTimeTransactions,                        Date = DateAdd(gblStartDate, 1, TimeUnit.Days) And Task = Value                    ),                    Duration                ),                0            ),            Tue: Coalesce(                Sum(                    Filter(                        colTimeTransactions,                        Date = DateAdd(gblStartDate, 2, TimeUnit.Days) And Task = Value                    ),                    Duration                ),                0            ),            Wed: Coalesce(                Sum(                    Filter(                        colTimeTransactions,                        Date = DateAdd(gblStartDate, 3, TimeUnit.Days) And Task = Value                    ),                    Duration                ),                0            ),            Thu: Coalesce(                Sum(                    Filter(                        colTimeTransactions,                        Date = DateAdd(gblStartDate, 4, TimeUnit.Days) And Task = Value                    ),                    Duration                ),                0            ),            Fri: Coalesce(                Sum(                    Filter(                        colTimeTransactions,                        Date = DateAdd(gblStartDate, 5, TimeUnit.Days) And Task = Value                    ),                    Duration                ),                0            )        }    ));

From there I start building the Canvas App.

Figure 1 – Canvas App

I won’t bore you with all the details 🥱, but a couple of things to call out.

In the Text Inputs that will show the Duration for each Day, I added code to the OnChange event to update the Collection with the edited value.

Figure 3 – OnChange event

I totaled up the Duration for each Task into a Text Input.

Figure 4 – Power FX code to sum Duration by Task

Super Early Bird
I totaled up the Duration for each Day into a Text Input.

Figure 5 – Power FX code to sum Duration by Day

To sum up the Duration both by Task and Day, I summed the values of the Text Inputs.

Figure 6 – Grand total for Duration

I added buttons for Add Row and Delete Row, and one more for Save Changes, the latter for persisting to an external data store.

That was really all it took to create an editable Matrix!

Figure 7 – Timesheet Canvas App

Check it out for yourself at https://mattruma.com/wp-content/uploads/2024/09/TimesheetSolution_1_0_0_0.zip. 👀

About the Author

Matt Ruma

Matt Ruma, Microsoft

Principal Cloud Solution Architect, Low Code, US Enterprise Manufacturing at Microsoft

Reference:

Ruma, M (2025). Adventures with Power Platform: Sample Timesheet Canvas App. Available at: Adventures with Power Platform: Sample Timesheet Canvas App – Matt Ruma [Accessed: 15th January 2025].

Share this on...

Rate this Post:

Share: