Periodic data comparison between TABLE 1 'industry...

Syndicate_Admin · ‎11-14-2023

I work in software, and with my current company, it's a very manual process in many ways. I came from API driven updates so, I am shocked and in need to create an easy, automated way to check for new and modified (and sometimes deleted) items.

I've managed to transform all the tables as needed, but now I need a comparison between them. This is driven by a few parameters:

TABLE 1 -- Source of truth
- Columns 1-5 are ALWAYS used in all environments.
  - This is compared with TABLE 2 (multiple variations/environments based on client)
TABLE 2-- Software data (with limited restrictions so the data can be manually modified)
- Columns 1-5 (if different) need to be replaced with TABLE 1 data and flagged for action.
  - ADD (if no match)
  - MODIFY (if variance in any 1-5 column)
- If match - ALL remainder columns (75 additional columns in total) are populated using TABLE 2 (software specific data including version keys for various fields to allow for accurate import) corresponding column.
- If no match - remainder columns are left blank.
End results is:
- Rows with no change (match)
- Rows added (no match)
- Rows modified (partial match)

This has be be repeated with TABLE 1 compared to TABLE 2 by cleint (multiple variations/environments based on client. I know how to 'copy' advanced editor to repeat steps, but I have no idea how to achieve this.

I've read several, several post, videos, articles, etc. and once again, I am also shocked there is not a standard code for this type of comparison. Maybe times have changed and you ahve something to share 🙂

Syndicate_Admin · ‎11-15-2023

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 nothing 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"},

Syndicate_Admin · ‎11-15-2023

what's the related key between these two table?

Syndicate_Admin · ‎11-21-2023

@wdx223_Daniel

I did figure out that I didn't have all my relationhsip connect in the data model. So, now, it is working well 🙂

I'd shre the code, but there are 22 tables worth and I am unsure hw to get the entire PBI file code at once --if it is possible.

One issue though, today, I added a 'date list' to it so that I could better manage dates, and when I tried to save it, it is showing this 'refresh' status error pasted below. All query are showing data with no error in transform view, but will not refresh 😞

Refresh

***Folder: Source Files

OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E.

**File: OPTUM_CPT_BASE_COVERAGE

OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E.

**File: OPTUM_HCPCS_BASE_COVERAGE

Load was cancelled by an error in loading a previous table.

*Appended: Optum CPT/HCPCS

OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E.

BHAR Product_Table