Grouping data table by multiple columns

[lvdbiggelaar]

Hello!

So I am still very new to Powerapps and I just seem not to be able to figure out how to get this right. I am trying to display my student attendance which is in a Sharepoint list. What I want to see is the student name and the status in the same row, and the dates must only show at the top. Each time attendance is taken I collect the date and their attendance status which is a tick, a cross, IN (Injured) or EX (Excused). Like this:

A row is then added to my Sharepoint list to capture each student. The dates can not be preset as they may change and the team list can also change.

I have tried a nested gallery to display it:

I have also tried a data table, which I had no idea how to group and get the headings to be the dates.

Does anyone have any advice???

[Pasta_K]

Hello, not too sure if this is what you were expecting, but you can try this out: 

This is essentially 2 galleries; 1 inside the other. The first one (The big one) will be grouped by dates. The inside gallery's items would then be "ThisItem.Date1" 

[lvdbiggelaar]

@Pasta_K thanks for this, I have tried it this the only problem with is is that by grouping by the date its harder to see the player's full attendance over the season. Also when I group it by the date the players name is repeated every time. 

I have tried grouping by name then date but that also doesn't give the desired outcome. I did this using a nested gallery and then also tried to achieve it using a data table but nothing worked. Is there a way to run a SQL query on a Sharepoint list through Powerapps?

Ideally I am trying to get it in this format:

[Pasta_K · Accepted solution]

Ah i see, not too sure about SQL query but this might be a workaround. (Sorry it looks a little ugly)

So theres 2 main galleries. The blue one is inside the green one. The red one serves simply as an external dates header.

The green one is grouped by name first. The blue gallery would have the same items source as the red gallery. Both grouped by dates. The only difference is the the labels in the red gallery would show the dates, while the one in the blue gallery would show whether the person was present or not by looking up the value to the name label (in the green gallery) and the date. Hope this makes sense and provides you with a fix to your issue!

Just in case i got a little confusing: 

Items

Red = Sort(GroupBy(TestList,"Date","Date1"),Date,Ascending)

Green = GroupBy(TestList,"Title","Title1")

Blue = Sort(GroupBy(TestList,"Date","Date1"),Date,Ascending)

p.s. "Title" is my names column

[lvdbiggelaar]

Thank you for this it does make sense...

In your Lookup function the "Date" you are using is that the column in the datasource? Which Date is it? 

[Pasta_K]

yup! its just another column in my data source. 

[lvdbiggelaar]

When I use that it says "String was not recognised as valid dateTime:

[lvdbiggelaar]

This is what the data source looks like:

 

[Pasta_K]

Can you try converting both into strings with the same format?

[lvdbiggelaar]

I started fresh and got it working!!! Thank you!!!!!