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

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
Super User
Super User

Re: Using GroupBy and AddColumns difficulty

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

Re: Using GroupBy and AddColumns difficulty

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'. 

 

Super User
Super User

Re: Using GroupBy and AddColumns difficulty

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.)

Community Support Team
Community Support Team

Re: Using GroupBy and AddColumns difficulty

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.

Re: Using GroupBy and AddColumns difficulty

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.

Re: Using GroupBy and AddColumns difficulty

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
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 349 members 4,515 guests
Please welcome our newest community members: