Compare Two Tables --> Auto Create Third Table

Hi jmetf150 ,

You can use Power BI to use the comparison analysis of the PO data as of snapshot date, but alternatively, your company's ERP system may be able to generate change log of PO as a standard report.  For example, SAP provides change logs for open Purchase Orders (POs).  To download PO change logs in bulk, you can query the CDHDR and CDPOS tables using SE16N, and export the results directly to Excel for database-like output.  You can filter by the last 1 day to see the change which occured in a business day to get the result you want. 

Reports like ME2N (List of POs) or ME80FN can list POs, but they do not natively include change logs.

Having said that, if you prefer to compare the snapshot data generated every morning from your ERP system as you described, I woud say Power Query is more suitable than dax.  

To compare the old_oor and new_oor tables in Power Query, you can perform a merge operation based on a common key, such as the PO column. This approach enables a side-by-side comparison of corresponding rows, facilitating the identification of changes in specific fields like Need and ETA.

Begin by loading both tables into Power Query. Select the old_oor table and navigate to the Data tab, then choose From Table/Range to load it into the Power Query Editor. Repeat this process for the new_oor table.

Once both tables are loaded, initiate the merge by selecting one of the tables, for instance, new_oor. In the Power Query Editor, go to the Home tab and click on Merge Queries. In the Merge dialog box, select old_oor as the second table to merge with. Choose the PO column in both tables to establish the matching criteria. Ensure that the columns selected for matching are of the same data type in both tables. For a comprehensive comparison, opt for a Full Outer Join under the Join Kind dropdown. This join type includes all rows from both tables, highlighting both matches and mismatches.

After executing the merge, a new column containing nested tables will appear. Expand this column by clicking on the expand icon (two opposing arrows) in its header. In the resulting menu, select the columns from old_oor that you wish to include in the comparison, such as Need and ETA. It's advisable to rename these expanded columns to distinguish them from those in new_oor, perhaps by appending a suffix like _Old.

With the tables merged and expanded, you can now add custom columns to compare the corresponding fields. For instance, to compare the Need dates, create a custom column with the following formula:

Need Change = if [Need] = [Need_Old] then "No Change" else "Changed"

Similarly, to compare the ETA dates, use:

ETA Change = if [ETA] = [ETA_Old] then "No Change" else "Changed"

To identify duplicates based on the PO field, add another custom column:

Duplicate = if [PO] = [PO_Old] then "Yes" else "No"

After creating these comparison columns, you can filter or sort the data as needed to focus on specific changes or duplicates. Once satisfied with the transformations, load the refined data back into Excel or your data model by selecting Home > Close & Load.

This method leverages Power Query's capabilities to merge tables and perform column-wise comparisons efficiently, providing a clear view of changes and duplicates between your datasets.

Best regards,