Combining Fields Into a Record

[NigelP]

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

[jhall]

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.  

😉

[Mr-Dang-MSFT]

---

@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]

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]

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]

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]

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]

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

[Mr-Dang-MSFT]

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]

Hi @Mr-Dang-MSFT

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

Thanks

Nigel