cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Create a Matrix type input in PowerApps (like an Excel sheet)

Hi All

 

We have a situation where we would like to allow users to populate data in a PowerApp using a matrix style input. By this I mean that each row in the gallery represents an entity (say a person) and the columns represent dates with the value being a value (e.g. time worked). Effectively each CELL (not row) is a record in the SQL database behind this.

 

Is this possible out of the box or via a 3rd party addin?

 

See the picture here for an example

2019-10-30_10-28-46.png

Thanks,

Craig

5 REPLIES 5
Highlighted
Super User
Super User

Re: Create a Matrix type input in PowerApps (like an Excel sheet)

@NewOzzie 

It can be done using a gallery but there's no way it can be explained here on the forum.  You would be best served by watching this Youtube tutorial instead.  Enjoy!

https://www.youtube.com/watch?v=xgznk4XlPCo

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Highlighted
Advocate I
Advocate I

Re: Create a Matrix type input in PowerApps (like an Excel sheet)

I have described how to do this in the next post, image and video in the one after (this post was a duplicate)

Highlighted
Advocate I
Advocate I

Re: Create a Matrix type input in PowerApps (like an Excel sheet)

Hi, so I have created an app that does this, turns out I can't post images / or youtube links without this post being rejected.

 

You have to group the data manually to get it into a matrix shape, then unfold it again once you're done. Because this is manual, you want to limit the number of columns you want to view in the app, as each column means repeating code. I chose 7, as we're looking at week long blocks of data.

 

Steps involved

1. Load filtered SharePoint list data into a collection on app start (so you're only getting 500 rows) - put this in App Start

 

// Set today's date
Set(var_date, Today());

// Collect data from SharePoint list (filter this appropriately)
ClearCollect(Col_timesheet, SharePointList)​

 

 

2. Set a target date (var_date), then set variables for the Mon - Sun dates; I have a datepicker to change the var_date as well

 

// Set the dates of the selected week, used in the gallery
Set(var_mon,var_date-Weekday(var_date,MondayZero));
Set(var_tue,var_date+1-Weekday(var_date,MondayZero));
Set(var_wed,var_date+2-Weekday(var_date,MondayZero));
etc.​

 

 

3. Pivot your data into a matrix shaped collection; this and step 2 are on a date change button ("Jump" in my video)

 

// Use GroupBy and AddColumns to move this into matrix format. The filter chops off the data based on the Monday - Sunday range to be displayed.
ClearCollect(Col_WeekMatrix, 
AddColumns(GroupBy(Filter(Col_timesheet,(Date >= var_mon) && (Date <= var_sun)),"Title","GroupedData"),
"Mon",First(Filter(GroupedData,var_mon=Date)),
"Tue",First(Filter(GroupedData,var_tue=Date)),
etc.​

//For this to work as an editable gallery, you can only have one entry per Title per date (hence First entry), you will need to sort out error handling for duplicates if they exist. If you are just reporting totals, replace First with Sum.

 

 

4. Create a gallery

 

//Gallery contents
Col_WeekMatrix​

 

 

5. Add a label in your gallery to display the row titles

 

//Row labels
ThisItem.Title​

 

 

6. Add 7 text inbox boxes in your gallery, to show the values for the days of the week. In my case I am displaying the timesheet hours booked.

 

//Text input box for Monday
ThisItem.Mon.Hours

//Text input box for Tuesday
ThisItem.Tue.Hours
etc.​

 

 

7. It is much easier for the PowerApp developer to patch each item OnChange, however I have found that it kills the app performance, you lose your text input focus and it screws up tab order. Instead I have a save button outside the gallery, which saves the gallery edits to a list shaped collection, which can be patched back to SharePoint.

 

//Create the list shaped table that can be patched back to SharePoint
Clear(Col_PatchBack);

// We are only patching back changes, so filter the gallery based on changes vs the matrix shaped collection that fed into the gallery
// You need to repeat this for each day (column) and append it to the list - there is no programmatic way to loop within PowerApps (Flow could do it)

Collect(
    Col_PatchBack,
    ForAll(
        GalleryTimesheetWeek.AllItems,
        If(
            Mon.Hours <> Value(TextInput_Mon.Text),
            {
                pbTitle: Label_Title.Text,
                pbHours: Value(TextInput_Mon.Text),
                pbID: Mon.ID,
                pbDate: var_mon,
            }
        )
    )
);

// Repeat the above code block 6 more times, changing Mon to Tue etc.​

 

 

8. You now have a collection shaped like your source list, which you can patch back to SharePoint or your datasource. I don't think you can use Patch alone, as you won't have all the same columns (well I couldn't get it to work!). I use pb... prefix so I don't have to think about disambiguation problems. 

 

// Clean up SharePoint list by removing any new zero hours records
RemoveIf(
    SharePointList,
    ID exactin ( Filter(
        Col_PatchBack,
        Or(
            pbHours = 0,
            IsBlank(pbHours),
            IsEmpty(pbHours)
        )
    ).pbID)
);

// Then remove the same records from the patchback collection
RemoveIf(
    Col_PatchBack,
    Or(
        pbHours = 0,
        IsBlank(pbHours),
        IsEmpty(pbHours)
    )
);

// Update existing items in SharePoint
ForAll(
    Filter(Col_PatchBack, Not(IsBlank(pbID))),
    UpdateIf(
        SharePointList,
        ID = pbID,
        {
            Hours: If(IsBlank(pbHours),0,pbHours),
            Date: pbDate,
        }
    )
);

// Finally add new items to SharePoint
ForAll(
    Filter(Col_PatchBack, IsBlank(pbID)),
    Patch(
        SharePointList,
        Defaults(SharePointList),
        {
            Title: pbTitle,
            Hours: If(IsBlank(pbHours),0,pbHours),
            Date: pbDate,
        }
    )
);​

 

*if someone can improve my code, please let me know. e.g. Remove / RemoveIf delegation issue.


There's a bunch of other stuff in the app, which I won't detail like using a timer to save back to SharePoint before refresh; some error handling; setting current user and patching that back to SharePoint. Let me know if you need a hand.

 

Cheers, Johnny

Highlighted
Advocate I
Advocate I

Re: Create a Matrix type input in PowerApps (like an Excel sheet)

Highlighted
Regular Visitor

Re: Create a Matrix type input in PowerApps (like an Excel sheet)

Hey @johnnyshield ,

This is a good sharing. 

However, since I am still a novice Powerapps developer, I cannot perfectly visualize the steps that you have made.

I would like to suggest if you could share how this can be done via a youtube tutorial so people like me can benefit the best from your sharing.

 

Thanks!

 

"I am a Novice Developer, but I am willing to learn from anybody"

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (12,625)