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

Combining Fields Into a Record

Hi

 

I am hitting my head against a Brick wall here.

 

I have a list called "Actuals" which has columns :-

  • Project
  • FY
  • FinancialItem (Actuals or Committed)
  • Internal
  • External

So each Project will have two records (Actual and Committed).

 

I want to combine the two records into one :-

 

  • Project
  • FY
  • InternalActual
  • ExternalActual
  • InternalCommitted
  • ExternalCommitted.

This helps me create a Sum :-

 

FY, InternalActual, ExternalActual,InternalCommitted,ExternalCommitted over all of the Projects and

Project, InternalActual, ExternalActual,InternalCommitted,ExternalCommitted over all of the FYs

 

Can anyone help ?

 

Thanks

 

Nigel

1 ACCEPTED SOLUTION

Accepted Solutions
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

14 REPLIES 14
jhall
Level 8

Re: Combining Fields Into a Record

This particular answer might apply to your scenario:

 https://powerusers.microsoft.com/t5/General-Discussion/Syntax-for-joining-tables/td-p/61387

 That is mainly about views vs. actually joining the data, but it likely applies.  I'll go ahead and try to connect this to your own scenario.

 One thing you might want to be clear about is if you're using a Table or a Collection.  This can get confusing. In fact, some of Microsoft's own documentation doesn't make a clear distinction between Tables and Collections.  I am going to cross into using a Collection for this below but will try to make it as clear as possible.

 Another thing is that some of the Table/Collection functions you might be trying to use do not actually modify the Table/Collection directly.  What this means is that functions like AddColumns() don't actually add columns to the Table.  They return a new Table that includes the old table plus the new columns.  So AddColumns(myTable,...) does not modify myTable.  You would use it more like: UpdateContext({newTable:AddColumns(myTable,...)}).

For the sake of argument, let's assume you have created a pair of tables (tblActuals and tblCommited) similar to this:

 

UpdateContext({tblActuals:Table({Project:"Project1",FY:2018,FinancialItem:"Play Dough",Internal:1000,External:40})});
UpdateContext({tblCommitted:Table({Project:"Project1",FY:2018,FinancialItem:"Play Dough",Internal:50,External:20})})

Then you have two tables w/ a single record that represent the Actuals and Committed values for a particular project/year (e.g. Project1 and 2018 respectively).  There could be hundreds of records in the table, but I just did one for simplicity.  I think that's what you stated that you're working with.  Two tables that represent the same project/year but are divided by whether they are Committed or Actual.

 

I can create a new Collection that contains everything from one of the two tables by doing something similar to this:

 

ClearCollect(colMerged,RenameColumns(tblActuals,"Internal","InternalActual","External","ExternalActual"))

 

 

To understand what happened here, in one command we empty and create a new Collection called colMerged and then we add all of the records from the table tblActuals, however, we change the names of the columns Internal and External first to represent your new names InternalActual and ExternalActual.

 

But we're missing the other new columns that include the Committed values.  We can modify the above command to include them like so:

 

 

 

ClearCollect(colMerged,RenameColumns(AddColumns(tblActuals,"InternalCommitted",0,"ExternalCommitted",0),"Internal","InternalActual","External","ExternalActual"))

This creates our new Collection as above, but also drops in two additional columns that we can use to merge everything in from the other table (i.e. tblCommitted).  Right now the table would contain all of the records from tblActuals, the two new columns needed for the Committed values, but they'd have Zero for their values.

 

 

But let's go ahead and bring it all together by using this:

 

 

ClearCollect(colMerged,RenameColumns(AddColumns(tblActuals,"InternalCommitted",LookUp(tblCommitted,Project=tblActuals[@Project] And FY=tblActuals[@FY],Internal),"ExternalCommitted",LookUp(tblCommitted,Project=tblActuals[@Project] And FY=tblActuals[@FY],External)),"Internal","InternalActual","External","ExternalActual"))

 

 

Now this is a super mouthful and confusing.  Also, doing references to values that are identically named across two tables is EXTRA confusing.  The last thing we're adding here is a Lookup() function that is going to get values from tblCommitted that match the Project and FY of the record we're currently adding from tblActual.  

 

LookUp(tblCommitted,Project=tblActuals[@Project] And FY=tblActuals[@FY]

The part that might be new to you is the [@Project] portion.  Essentially, in English, we're looking up a record from the table tblCommitted where the column Project / FY from that table matches the column Project / FY from the record within tblActuals that we're currently adding to the new collection colMerged.

 

Sooooo easy.  

 

😉

PowerApps Staff Mr-Dang-MSFT
PowerApps Staff

Re: Combining Fields Into a Record


@NigelP wrote:

Hi

 

I am hitting my head against a Brick wall here.

 

I have a list called "Actuals" which has columns :-

  • Project
  • FY
  • FinancialItem (Actuals or Committed)
  • Internal
  • External

So each Project will have two records (Actual and Committed).


 

Can you clarify this part? I'm not picturing this table. A table of dummy data would help here.

NigelP
Level 8

Re: Combining Fields Into a Record

Hi @Mr-Dang-MSFT

 

I want to go from this =>

 

 

Actuals

 

Project ID         FY          ItemType        CAPEX          OPEX

 

Proj1                FY18/19    Actual         $150,000    $10,000

Proj1               FY18/19     Commit      $20,000      $30,000   

 

To =>

 

Project ID       FY      ActualCAPEX      ActualOPEX     CommittedCAPEX    CommittedOPEX

 

Proj1         FY18/19     $150,000         $10,000            $20,000                     $30,000

 

REgards

 

NIgel

jhall
Level 8

Re: Combining Fields Into a Record

My apologies Nigel, I actually wrote you a looong reply (which for some reason was marked to be "reviewed" and has never yet appeared).  Also, we had an enjoyable -25C weather here and a power outage, leading my PowerApps session that I'd build your solution in to close down when my backup battery ran out of juice.

 

So while I'm not going to go to the trouble of re-writing my full page explanation of how to do this (and the Microsoft folks haven't approved my reply), here's a link to a discussion on what you're wanting to do:

 

https://powerusers.microsoft.com/t5/General-Discussion/Syntax-for-joining-tables/td-p/61387

 

 

Mike8
Level 10

Re: Combining Fields Into a Record

Hello NigelP,

Does this work for you?

 

If you need it in a collection..
ClearCollect(NewCollection,ForAll(Filter(Actuals,ItemType="Actual"),{ProjectID:ProjectID,FY:FY,ActualCAPEX:CAPEX,ActualOPEX:OPEX,CommittedCAPEX:LookUp(Actuals,ProjectID=Actuals[@ProjectID]&&ItemType="Commit",CAPEX),CommittedOPEX:LookUp(Actuals,ProjectID=Actuals[@ProjectID]&&ItemType="Commit",OPEX)}))

If you need the data in a gallery it's easier..
Gallery1.Items = Filter(Actuals,ItemType="Actual")
and you add two more labels with the two LookUps above


jhall
Level 8

Re: Combining Fields Into a Record

But it looks like I remembered most of the code.  Assuming you have two tables of data named tblActuals and tblCommitted:

UpdateContext({tblActuals:Table({Project:"Project1",FY:2018,FinancialItem:"Play Dough",Internal:1000,External:40})});
UpdateContext({tblCommitted:Table({Project:"Project1",FY:2018,FinancialItem:"Play Dough",Internal:50,External:20})})

Then you can merge them into a single table tblMerged like so:

UpdateContext({tblMerged:RenameColumns(AddColumns(tblActuals,"InternalCommitted",LookUp(tblCommitted,Project=tblActuals[@Project] And FY=tblActuals[@FY],Internal),"ExternalCommitted",LookUp(tblCommitted,Project=tblActuals[@Project] And FY=tblActuals[@FY],External)),"Internal","InternalActual","External","ExternalActual")})

To break down what this is doing, you kind of work inside out:

  1. We are Adding InternalCommitted and ExternalCommitted columns to tblActuals and filling them w/ Lookup values from tblCommitted where the FY and Project match.
  2. We are renaming the tblActuals column headers from/to Internal/InternalActual and External/ExternalActual.
  3. We are assigning that to a new table named tblMerged (which you can use to assign to the Items value of any DataTable you add to your app.

I had noted in my other reply, that the problem you might be struggling with is that the various table functions do not directly modify the table itself.  For example, running RenameColumns(X...) does not actually rename any of the columns in X.  It simply returns a copy of that table in memory that can then be assigned to something else.  That's what we're doing when we do the assignmend to tblMerged.

NigelP
Level 8

Re: Combining Fields Into a Record

Hi @jhall

 

I really appreciate you going beyond the call of duty and having a backup generator so you can carry on working !

 

It was the last stage (tblMerged) that i was having problems with.

 

I will now test and get back to you with the results.

 

Regards

 

Nigel

PowerApps Staff Mr-Dang-MSFT
PowerApps Staff

Re: Combining Fields Into a Record

Thanks for the many responses @jhall @Mike8. My 2 cents: if you have your data stored on SQL, you can create SQL Views where you join the data together.

 

Otherwise, you can Group() the data by distinct Projects. Then you can reference the data inside as you need. Let me know if you do not have SQL and I can go into this.

NigelP
Level 8

Re: Combining Fields Into a Record

Hi @Mr-Dang-MSFT

 

I am using SharePoint lists so I would be interested in your kind offer about Groups.

 

Thanks

 

Nigel

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 (5,141)