Complex Merge Collections Question

[Anonymous]

I have 2 collections that I have successfully merged into a single collection.  The only issue is that one of the columns (

"Opportunity_x002d_Update") is not present.  I have tried everything I can think of to get this additional column added but I am out of ideas. Any recommendations are appreciated. 

[RandyHayes]

@Anonymous 

The way you are merging your collections, you will not see that as a column.  All of your results from the merge will be in the Pursuit... column that you are adding, and it would only be whatever is in "Pursuit..." from the second collection.  What is significant is that your picture shows nothing in that column (it should show a value there), so there appears to be an issue with your LookUp formula.

If you want to actually have those as top level columns, you will need to put this together differently.  This can be done with a Patch statement - which will patch two records together into one.

Because your column names are long, I will shorthand the names in the sample here:

ClearCollect(
    ForAll(ShowColumns('Deal-Intake-App', "Title", "Close..", "SFDC..Name", "SFDC...ID", "Sector", "TCV") As _item,
        Patch(_item,
              LookUp(
                 ShowColumns('Deal-Updates', "Deal...", "Pursuit...", Opportunity..."),
                 Deal... = _item.Title
              )
        )
    )
)

This should give you what you are looking for, and if it still doesn't then you need to look at your lookup criteria as something would not be matching with the Title and Deal...ID

I hope this is helpful for you.

[Anonymous]

@RandyHayes - I have tried every idea I have and the code has too many formulas and errors for me to resolve.  It seems odd to me it requires so much code to merge all columns from two collections. Is there a method for loading two SP Lists into a single collection? 

ClearCollect(
    ForAll(ShowColumns('Deal-Intake-App', "Title", "Close_x002d_Date", "SFDC_x002d_Opp_x002d_Name", "SFDC_x002d_Opp_x002d_ID", "Sector", "TCV") As _item,
        Patch(_item,
              LookUp(
                 ShowColumns('Deal-Updates', "Deal_x002d_Ref_x002d_ID", "Pursuit_x002d_Closedown_x002d_Da", "Opportunity_x002d_Update"),
                 item.Title = Deal_x002d_Ref_x002d_ID
              )
        )
    )
);

[RandyHayes]

@Anonymous 

It's all about the JSON structure underneath.  When you access a datasource you are getting back a table with records.  Those have a defined structure and schema.  If you have two sources that are exactly the same (very rare) then yes, you can pull them all into a collection (although I would question why). Now I say this in the sense of having a common schema in your app.  If you have a datasource with these columns: ID, Title, Name, Numbers and another datasource with: ID, Title, Account, Amount and you try to pull them together, you will have a resulting table with records of : ID, Title, Name, Number, Account, Amount.

If Account is the same as Name and Amount is the same as Numbers, then you can "shape" the data with formulas.  PowerApps provides tools like AddColumns, RemoveColumns, GroupBy, ShowColumns, Patch, etc.  that will allow you to shape records schemas.  So:

   Collect(collectionName, datasource1, RenameColumns(datasource2, "Account", "Name", "Amount", "Numbers"))

Would then have a table of : ID, Title, Name, Numbers where the table includes all the records from the two datasources.

Patch let's us combine records from left to right.  So, Patch({Title: "Hi", Number:2}, {Title: "Bye", Amount: 10}) would give you a record of {Title: "Bye", Number:2, Amount:10}

Notice how it combined columns from left to right and included columns from all records.

So, in our formula (and I now realize I neglected one important point...it was missing a collection name):

ClearCollect(YourCollection,
    ForAll(ShowColumns('Deal-Intake-App', "Title", "Close_x002d_Date", "SFDC_x002d_Opp_x002d_Name", "SFDC_x002d_Opp_x002d_ID", "Sector", "TCV") As _item,
        Patch(_item,
              LookUp(
                 ShowColumns('Deal-Updates', "Deal_x002d_Ref_x002d_ID", "Pursuit_x002d_Closedown_x002d_Da", "Opportunity_x002d_Update"),
                 item.Title = Deal_x002d_Ref_x002d_ID
              )
        )
    )
);

We are using ForAll (which returns a Table) to supply the collection with its table.  As the record source for the ForAll (and you need a table for a ForAll), we are taking the items from 'Deal-Intake-App' which we have shaped to remove all but the listed columns.  We call it _items for reference to it later.

Now, in the ForAll, what we are doing is Patching the two records together.  The first record is the one we just got from the ForAll and called _item.  The second record to patch will be the Record that is returned from the LookUp (which is based on 'Deal-Updates' with only the columns listed returned).

Just as in the example above of the simple record patch, Patch will combine the two records together.  Since there are no similar column names between the two records, all of the columns will be included in the resulting record.

So, your final table records will be these columns:

"Title", "Close_x002d_Date", "SFDC_x002d_Opp_x002d_Name", "SFDC_x002d_Opp_x002d_ID", "Sector", "TCV", "Deal_x002d_Ref_x002d_ID", "Pursuit_x002d_Closedown_x002d_Da", "Opportunity_x002d_Update"

I hope that is relatively clear and understandable. 

[Anonymous]

@RandyHayes - Thanks for the great explanation!  Your updated code is only showing one error related to the use of a period (item.Title) as shown below.