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

Re: Combining Fields Into a Record

I'll call this Table1:

Project ID FY ItemType CAPEX OPEX
Proj1 FY18/19 Actual $150,000 $10,000
Proj1 FY18/19 Commit $20,000

$30,000

Proj2 FY18/19 Actual $250,000

$20,000

Proj2 FY18/19 Commit $30,000

$40,000

 

[Side note: you can paste in spreadsheets from Excel 🙂 ]

 

I can group together records that are matching in the columns I choose:

 

GroupBy(Table1,"Project Id", "FY", "GroupName")

This means, "Group together records that have distinct combinations of Project Id AND Fiscal Year. Move the grouped records with all the other columns into a new column called 'GroupName.' The grouped records will result in a nested table."

 

 

Here's what the outer table would look like:

Project ID FY GroupName
Proj1 FY18/19
ItemType CAPEX OPEX
Actual $150,000 $10,000
Commit $20,000 $30,000
Proj2 FY18/19
ItemType CAPEX OPEX
Actual $250,000 $20,000
Commit $30,000 $40,000

 

Now you have the data where you want it. You can AddColumns that can reach into each group. You can set up a gallery's Items like this:

 

AddColumns(
    GroupBy(Table1,"Project Id", "FY", "GroupName"),
"InternalActual",LookUp(GroupName,ItemType="Actual",CAPEX),
"ExternalActual",LookUp(GroupName,ItemType="Actual",OPEX),
"InternalCommitted",LookUp(GroupName,ItemType="Committed",CAPEX),
"ExternalCommitted",LookUp(GroupName,ItemType="Committed",OPEX)
)

 

 

Because GroupBy is like Distinct and other non-delegable functions, it may not return what you're looking for if you use it directly on the datasource. For this to work, I would suggest collecting the data you're looking at to a collection. 

 

And if you really want to tidy up, you can remove the nested tables (the grouped records):

DropColumns(
    AddColumns(
        GroupBy(Table1,"Project Id", "FY", "GroupName"),
    "InternalActual",LookUp(GroupName,ItemType="Actual",CAPEX),
    "ExternalActual",LookUp(GroupName,ItemType="Actual",OPEX),
    "InternalCommitted",LookUp(GroupName,ItemType="Committed",CAPEX),
    "ExternalCommitted",LookUp(GroupName,ItemType="Committed",OPEX)
    ),
"GroupName"
)

View solution in original post

Highlighted
Post Prodigy
Post Prodigy

Re: Combining Fields Into a Record

Hi @Mr-Dang-MSFT

 

That worked perfectly.  Thank you very much indeed.

 

Also thanks to @jhall and @Mike8 for their input as well.  It all helped.

 

Regards

 

Nigel

Highlighted
Advocate V
Advocate V

Re: Combining Fields Into a Record

I'm coming across the same issue here, I'm not quite understanding the resolution.

Is it possible to see the full ClearCollect and GroupBy Statements, please ? I think that will help!

Thank you!

Highlighted
Post Prodigy
Post Prodigy

Re: Combining Fields Into a Record

Hi @vsslasd

 

You take Mr Dang's last set ofPowerShell Commands and add "ClearCollect(collGroupedProjects,"      to the front of it and ")" at the end and you have the complete set.

 

You can see the result by View -> Collections and selecting collGroupedProjects.

 

Regards

 

Nigel

Highlighted
Advocate V
Advocate V

Re: Combining Fields Into a Record

Thank you!

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Users online (6,314)