Merge two tables with new or updated row values

[Anonymous]

I have a `History table` in an Excel sheet that i want to merge with a `Salesforce table` (connection only) that refreshes and brings in New or Updated rows. I found this article interesting, but it requires both the tables to have the same number of columns. It also uses an `Anti-join` and displays only any new or updated rows as a `new table` in an Excel sheet. I want to update this `History table` with new or changed data, instead of creating a new table.

The `History table` has 14 columns ie. 11 standard columns and 3 Manual entry columns.

WB Study ID

Commissioning Country

Project Name

Field Start Date

Study Delivery Date

Client

Local Amount

Local Currency

IBP

Finance Coordinator

Study Status

Manual col 1

Manual col 2

Manual col 3

 

The `Saleforce table` has say 11 columns that match with the `History table`.

WB Study ID

Commissioning Country

Project Name

Field Start Date

Study Delivery Date

Client

Local Amount

Local Currency

IBP

Finance Coordinator

Study Status

I only want the new or updated rows to show up in the `History table` using a `Left or Right Outer Join`, leaving the additional 3 manual-entry columns blank.

The M-code that is interesting for the `History table` from the article and that which i want to change to only pick values from the 1st 11 columns (excluding the 3 manual-entry columns), is this one:

= Table.AddColumn(ReorderedColumns, "HistoryKey", each Text.Combine(
                List.Transform(
                    
                        Record.FieldValues(_), 
                    each Replacer.ReplaceValue(
                        Text.From(_),
                        null,
                        "")),
                ";"))

However, it picks up and concatenates the row values from all the History table columns and not just the first 11 columns to create a `History Key` column that is then used in Merge.

Eventually what i want to do is:

- create a "New Key" column (using above code) in `Salesforce table`

- create a "History Key" column (using above code) in `History table`

- Merge the 2 tables on these keys that should update the `History table` with new or updated rows.

- finally delete the "History key" column from the `History table`

- dump the `History table` back to the same worksheet (i.e. update the worksheet table)

Any help would be most appreciated in making this happen.

[Anonymous]

@AlexisOlson this is the same issue. Can you help me with this one if its possible?

[AlexisOlson]

Not easily without an actual file to work with. (Dummy data is fine.)

[Anonymous]

I have tagged in another similar post, where i have shared screenshots to the best of my knowledge. Would that work for you?

Edit: I have also attached a GDrive link to Sample workbook now in that post.

[v-jingzhang · Accepted solution]

Hi @Anonymous 

As you have another post discussing this problem, can you please close this one? Do you need further help with this post?

Best Regards,
Community Support Team _ Jing

[Anonymous]

Anyone who can help quickly with this issue?

[wdx223_Daniel]

try this code:

NewStep=HistoryTable&Table.RemoveMatchingRows(SalesforceTable,Table.ToRecords(Table.SelectColumns(HistoryTable,Table.ColumnNames(SalesforceTable))))

[Anonymous]

Hi @wdx223_Daniel i have updated my post to make it more column listing the columns from both tables. Let me know if you need anything else.

[Anonymous]

Hi @wdx223_Daniel , where do i put this step? If i put in History table, it gives `Expression.Error: A cyclic reference was encountered during evaluation.`

Basically, i want to modify the above m-code (in my post) for that extra `HistoryKey` column in History table, which will only contain values from the 1st 12 rows, excluding the remaining 19 (right now, it is picking up all 31 column values). This way, i would be able to merge new/updated data from Salesforce table into the History table on the New key & History key (as they would contain only the values from the 1st 12 columns and good to match).