Compare Two Tables --> Auto Create Third Table

[jmetf150]

Hello,

I have an open order report generated every morning for open purchase orders. I need to track changes in the dates from day to day (both need and ETA). Additionally, I would like to monitor how many times the date changes while the PO is open until it is closed.

I want to compare the old report (old_oor) to the new report (new_oor) and have PowerBI create a third table (output_oor) that updates old dates with new dates for the same PO number and appends any new POs to the list.

My final step in the process, if it is doable, would be for PowerBI to automatically remove the PO if the due date is greater than 2 years.

Below is a link to my sample PBIX file:

Sample Data.pbix 

Thanks in advance!

Jonathan

[speedramps · Accepted solution]

Hi Jonathan

This is common question ... how to compare old files with new files.

You will need to learn about SCD (Slow Changing Dimension) especially type 2

https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

also see wiki and youtube videos for SCD (Slow Changing Dimension) especially type 2

You will need to build some kind of front end process to date/time stamp your new records every day and append them to a master file.

So you have a single file with all the records for today and all the previous days, each with a date/time stamp.

Also consider have an indicator flag for the current record.

Comparing the current records for a purchase order with a previous records is then easy, because they are all in the same table.

Hope that make sense. Reach out if you need more info.

Please clcik the [accept solution] and [thumbs up buttons].

At the very least click the thumbs up to acknowledge the idea, thank you.

[DataNinja777 · Accepted solution]

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,

[speedramps · Accepted solution]

Hi Jonathan

This is common question ... how to compare old files with new files.

You will need to learn about SCD (Slow Changing Dimension) especially type 2

https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

also see wiki and youtube videos for SCD (Slow Changing Dimension) especially type 2

You will need to build some kind of front end process to date/time stamp your new records every day and append them to a master file.

So you have a single file with all the records for today and all the previous days, each with a date/time stamp.

Also consider have an indicator flag for the current record.

Comparing the current records for a purchase order with a previous records is then easy, because they are all in the same table.

Hope that make sense. Reach out if you need more info.

Please clcik the [accept solution] and [thumbs up buttons].

At the very least click the thumbs up to acknowledge the idea, thank you.