Looking up data to compare when not on the same row

Hi JemmaD ,

Here’s a full Power Query (M) script that you can drop straight into the Advanced Editor. It assumes your original table is named Source, with columns: Unique ID, Product, Previous Product, Cost, Previous Cost.

This will transform your data to show for each product under each Unique ID whether it’s New, Removed, or Unchanged, and bring in the cost comparison.

let
    Source = YourOriginalTableHere,  // Replace with actual source name if different

    // Step 1: Unpivot Product and Previous Product
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Unique ID", "Cost", "Previous Cost"}, "Attribute", "Product Name"),
    
    // Step 2: Add Source column (Current or Previous)
    AddSource = Table.AddColumn(Unpivoted, "Source", each if [Attribute] = "Product" then "Current" else "Previous"),
    
    // Step 3: Remove unnecessary columns and nulls
    RemovedCols = Table.SelectColumns(AddSource, {"Unique ID", "Product Name", "Source"}),
    RemovedNulls = Table.SelectRows(RemovedCols, each [Product Name] <> null),
    
    // Step 4: Group by Unique ID and Product Name
    Grouped = Table.Group(RemovedNulls, {"Unique ID", "Product Name"}, {{"AllRows", each _, type table [Unique ID=nullable text, Product Name=nullable text, Source=nullable text]}}),
    
    // Step 5: Add Status column
    AddStatus = Table.AddColumn(Grouped, "Status", each 
        let
            sources = List.Distinct(List.Transform([AllRows], each [Source]))
        in
            if List.Contains(sources, "Current") and List.Contains(sources, "Previous") then "Unchanged"
            else if List.Contains(sources, "Current") then "New"
            else "Removed"
    ),
    
    // Step 6: Expand back the table
    Expanded = Table.ExpandTableColumn(AddStatus, "AllRows", {"Unique ID", "Product Name"}),
    RemoveDupes = Table.Distinct(Expanded),

    // Step 7: Merge in Current cost
    CurrentTable = Table.SelectColumns(Source, {"Unique ID", "Product", "Cost"}),
    RenamedCurrent = Table.RenameColumns(CurrentTable, {{"Product", "Product Name"}, {"Cost", "Current Cost"}}),
    MergedWithCurrent = Table.NestedJoin(RemoveDupes, {"Unique ID", "Product Name"}, RenamedCurrent, {"Unique ID", "Product Name"}, "CurrentCostTable", JoinKind.LeftOuter),
    ExpandCurrent = Table.ExpandTableColumn(MergedWithCurrent, "CurrentCostTable", {"Current Cost"}),

    // Step 8: Merge in Previous cost
    PreviousTable = Table.SelectColumns(Source, {"Unique ID", "Previous Product", "Previous Cost"}),
    RenamedPrevious = Table.RenameColumns(PreviousTable, {{"Previous Product", "Product Name"}, {"Previous Cost", "Previous Cost"}}),
    MergedWithPrevious = Table.NestedJoin(ExpandCurrent, {"Unique ID", "Product Name"}, RenamedPrevious, {"Unique ID", "Product Name"}, "PreviousCostTable", JoinKind.LeftOuter),
    ExpandPrevious = Table.ExpandTableColumn(MergedWithPrevious, "PreviousCostTable", {"Previous Cost"}),

    // Step 9: Add Cost Change column
    AddCostChange = Table.AddColumn(ExpandPrevious, "Cost Change", each try [Current Cost] - [Previous Cost] otherwise null)

in
    AddCostChange

Replace YourOriginalTableHere with your actual source table if it's named something else like Excel.CurrentWorkbook(){[Name="YourTable"]}[Content].

Once you paste this into the Advanced Editor, you’ll get a clean table showing each product per Unique ID, a status of New, Removed, or Unchanged, along with current and previous costs and the difference.

Best regards,