No takers?
I do have a bit more details now, too., that may help someone understand and be able to assist.
What I want is similar to this post, Merge and keep rows that don't match - Microsoft Fabric Community but based on multiple columns: When COLUMN 1 or COLUMN 2 or COLUMN 3 or COLUMN 4 or COLUMN 5 are a mismatch, then show on merged output.
The merged output should be based on when COLUMN 1 (primary key), COLUMN 2, COLUMN 3, COLUMN 4, or COLUMN 5 do not match. Any row in Table 1 that has different values in any of these columns compared to the corresponding row in Table 2 should be considered a mismatch.
To identify the action for each mismatched line, I think I would need a conditional column to Identify the Action for Each Mismatched Line.
Here is an example (I don't know M code well, so this code doesn't work, but I hope it helps explain what I want)
Action = IF(ISBLANK([Table 2: COL 1, COL 2, COL 3, COL 4, COL 5]),"ADD", ""
and
Action = IF(ISBLANK([Table 1: COL 1, COL 2, COL 3, COL 4, COL 5]),"DELETE", ""
and
Action = IF([Action] = "" & [Table 2: COL 1 or COL 2 or COL 3 or COL 4 or COL 5]><[Table 2: COL 1 or COL 2 or COL 3 or COL 4 or COL 5]),"MODIFY", "No Change"
"No Change" should technically not occur, because the merge should only have rows that mismatch.
This formula is intended to return Add if the corresponding column 1-5 rows in Table 2 are blank (no match in table 2) and return DELETE if the corresponding column 1-5 rows in Table 1 are blank (no match in table 1). Then, when action is not ADD or DELETE (it is [blank]) and any of the columns are not equal, return Modify (only a partial match).
All of the columns for each table are listed below. Note that Column 2, Product ID, is the common primary key):
- The blue columns of each table should always match in both and the sole purpose of the merge to check these 5 values. When any of the 5 values is a mismatch, Table 1 is the value to populate into table 2.
- The red columns only exist in tabel 2 as they hold the specific keys, version, etc. within the software database and will be blank for 'ADD' and populated for DELETE or MODIFY.
Table 1: {"Product_qualifier", "Product_id", "Product_name", "effective_date", "termination_date"}
Table 2: {"Product_qualifier", "Product_id", "Product_name", "effective_date", "termination_date", "default_class", "incentive_flag", "generic_product_id", "generic_name", "manufacturer_id", "manufacturer_name", "generic_code", "DEA_class_code", "Therapeutic_class_code", "Therapeutic_equiv_code", "RX_OTC_indicator", "third_party_rest_code", "GPPC_code", "metric_strength", "strength_UOM", "dosage_form", "package_size", "package_UOM", "package_QTY", "total_package_QTY", "legend_change_date", "DESI_code", "maintenance_drug_code", "dispensing_unit_code", "unit_dose_code", "route_admin_code", "form_type_code", "dollar_rank_code", "RX_rank_code", "single_comb_code", "repackager_IND", "product_id.1", "product_name.1", "product_id2", "product_name2", "last_change_date", "drug_status", "int_EXT_Code", "package_description", "OTC_EQUIV_IND", "stc_code", "gcn_code", "HICL_SeqNo", "GTC_Code", "HIC3_Code", "Quadrant_Req", "Tooth_Req", "Surface_Req", "tooth_type", "Auto_Adjudicate", "Clinical_Doc", "Credentialling_Req", "color_code", "hist_multiplier", "min_num_surfaces", "max_num_surfaces", "Time_Units", "asc_grouper", "apc_classification", "base_units", "Default_Unit_Basis", "RoomAndBoardCharges", "Action", "Entity", "Key_1", "Key_2", "Key_3", "Key_4", "Key_5", "Key_6", "Key_7", "Key_8", "Key_9", "Key_10", "DateTimeModified"},
