cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerApps Staff Mr-Dang-MSFT
PowerApps Staff

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

NigelP
Level 8

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

vsslasd
Level 8

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!

NigelP
Level 8

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

vsslasd
Level 8

Re: Combining Fields Into a Record

Thank you!

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,683)