Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
@AlexisOlson this is the same issue. Can you help me with this one if its possible?
Not easily without an actual file to work with. (Dummy data is fine.)
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.
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
Anyone who can help quickly with this issue?
try this code:
NewStep=HistoryTable&Table.RemoveMatchingRows(SalesforceTable,Table.ToRecords(Table.SelectColumns(HistoryTable,Table.ColumnNames(SalesforceTable))))
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.
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).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |