cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cardinalpipkin
Responsive Resident
Responsive Resident

Using GroupBy and AddColumns difficulty

Hi,

I'm trying to convert an SP list in PowerApps to this:
dsfg.PNG

 

This has the Title along the left as you can see, redacted in blue. The months along the top and whether the item has a RAG (Red Amber Green) status. As you can see I've successfully done this in a web part using javascript, views and custom column formatting. But it's slow to respond so am turning to PowerApps.

 

Here's the data shown in a PowerApps table:

fdean.PNG

 

So to be as clear as I can - the ServiceStatus is the thing which will show the color. The Service will show along the left most column and the months will be grouped as unique items, for example Jan19|Feb19|Mar19 along the top.
I've attempted to use GroupBy and AddColumns but failed.

C

 

 

 

 

 

 

6 REPLIES 6
LRVinNC
Super User
Super User

Looks like your post got cut off... what sort of problem are you having with AddColumns and Group By? 

LRVinNC

------------------
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Hi, THe problems I've had are the arguments that they require. I just don't understand what is required. I've tried putting in the fields that I want to show but it says 'Text expected'. 

 

Here's an example from one of my apps:

 

GroupBy.jpg

I recommend building a complex forumula like this from the inside out.  Build each part independently first to make sure it works, and then combine into the layers you really want, starting with the inner most portion and layering on each additional action, one at a time, to ensure it still works.  

 

Make sure you have .Value on columns, where necessary, to access the actual value as I did above.  (Department and Project above are both lookup columns.)

LRVinNC

------------------
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
v-xida-msft
Community Support
Community Support

Hi @cardinalpipkin ,

Could you please share a bit more about the Month column in your SP list? Is it a Single line text type column?

Could you please share more details about the GroupBy and AddColumns formula you used within your app?

 

Based on the needs that you mentioned, I think the three Gallery controls could achieve your needs. I have made a test on my side, please take a try with the following workaorund:5.JPG

 

6.JPG

 

7.JPG

Set the Items property of the first Gallery (Gallery1) to following:

Distinct('20190410_case15',Month)   /* <-- '20190410_case15' represents my SP list data source */

On your side, you should type:

Distinct('YourSPList',Month)

Add a Label control within this Gallery, set the Text property to following:

ThisItem.Result

 

Set the Items property of the first Gallery (Gallery2) to following:

Distinct('20190410_case15',Title)

On your side, you sould type:

Distinct('YourSPList',Title)

Add a Label control within this Gallery, set the Text property to following:

ThisItem.Result

Within the Gallery2, add a Gallery3, set the Items property to following:

AddColumns(Distinct('20190410_case15',Month),"TitleValue",ThisItem.Result)

On your side, you should type:

AddColumns(
Distinct('YourSPList', Month),
"TitleValue",
ThisItem.Result
)

Set the TemplateFill property of the Gallery3 to following:

If(
    LookUp('20190410_case15', Title=ThisItem.TitleValue&&Month=ThisItem.Result,ServiceStatus.Value)="Stable",
    RGBA( 46, 139, 87, 1 ),
    LookUp('20190410_case15', Title=ThisItem.TitleValue&&Month=ThisItem.Result,ServiceStatus.Value)="Low Risk",
    RGBA( 255, 165, 0, 1 ),
    RGBA( 255, 255, 255, 1 )
)

On your side, you should type:

If(
    LookUp('YourSPList', Title=ThisItem.TitleValue && Month=ThisItem.Result, ServiceStatus.Value)="Stable",
    RGBA( 46, 139, 87, 1 ),
    LookUp('YourSPList', Title=ThisItem.TitleValue && Month=ThisItem.Result, ServiceStatus.Value)="Low Risk",
    RGBA( 255, 165, 0, 1 ),
    RGBA( 255, 255, 255, 1 )
)

Note: The ServiceStatus column is a Choice type column in my SP list. The Gallery3 is a nested Gallery within Gallery2.

 

Please check the attached sample app for more details:

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for the wonderful and detailed responses. I will make time to try them and report back if successful or not.

Thanks and thanks again.

Hi Kris - is that an example PowerApp that you attached? I've attempted to upload it to PowerApps but it said it needs to be a zip. I zipped it but it still doesn't like it, it says 'Something went wrong'.....helpful eh?

 

C

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (29,228)