Looking up data to compare when not on the same row

[JemmaD]

Hi there, 

I have a data table with a unique id, then an attribute for current product, previous product, current cost, previous cost etc.

Each unique id can have more than one row per product, cost and other attributes, therefore to compare whether the product appears in both the current and previous iteration I cannot compare at row level, rather I need to look across the data at unique id level, look up all products and previous products found, and compare. Then return an indicator of some sort to say that it appears in current but not previous, or vice versa, and then I should be able to compare the current and previous cost and can indicate the product change.

I've played about with unpivoting the data in Power Query so I get product/previous product in one column, the product name in another column, and unique id in a third column. But, that's as far as I get before I get stuck on what to do, or even if this is the way forward.

Can anyone give me an idea of how to achieve my desired result? 

Example Data

Unique ID	Product	Previous Product	Cost	Previous Cost
A0001	Apple	Pear	£100	£75
A0001	Pear	Apple	£150	£100
A0001	Banana	null	£20	null
A0002	Apple	Apple	£100	£75
A0002	Orange	null	£300	null

[v-achippa]

Hi @JemmaD,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @DataNinja777 and @danextian for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user resolved your issue? or let us know if you need any further assistance.
If any response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @JemmaD,

We wanted to kindly follow up to check if the solution provided by the user resolved your issue.
If any response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @JemmaD,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user resolved your issue.
If any response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[danextian · Accepted solution]

Hi @JemmaD 

The unique ID alone will not tell us which record comes first. There should be an indicator column for such like an update or change timestamp. I wouldn't use Power Query to get the previous row's value as it is not optimized for scanning tables but depending on the size of the dataset, I would use a combination of both DAX and M for an optimized calculation. 

In the below, I example I used an index column to indicate the order of the records and which will be used in calculating the previous value. I am unsure though how you got Pear as the previous product in row 1 when there isn't anything before that.

Previous Product = 
MAXX (
    FILTER (
        'Table',
        'Table'[UniqueID] = EARLIER ( 'Table'[UniqueID] )
            && 'Table'[Index]
                = EARLIER ( 'Table'[Index] ) - 1
    ),
    [Product]
)

Please see the attached pbix.

[Ashish_Excel]

Hi,

For your to analyse this data, show the structure in which you would like to transform this data into.

[Ashish_Excel]

Hi,

For your to analyse this data, show the structure in which you would like to transform this data into.

[DataNinja777]

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,

[JemmaD]

Hi @DataNinja777  thank you for this, I am copying your steps but when I get to AddStatus I am getting an error in that field of Expression.Error: We cannot convert a value of type Table to type List.
Details: Value=[Table] Type=[Type]