Determining primary value from column

Hi mryoan04 

I'd encourage you to use AI to generate M code for this. Below is the code AI shared with me and it works! 

let
    Source = YourTable,
    // Step 1: Split each row into a list of records [Number, Ingredient]
    AddParsedList = Table.AddColumn(Source, "Parsed List", each 
        List.Transform(
            Text.Split([Ingredients], " | "),
            (item) => 
                let 
                    parts = Text.Split(item, ";")
                in 
                    [Number = Number.FromText(parts{0}), Ingredient = parts{1}]
        )
    ),

    // Step 2: Add a column that gets the max number for each row
    AddMaxNumber = Table.AddColumn(AddParsedList, "MaxNumber", each 
        List.Max(List.Transform([Parsed List], each _[Number]))
    ),

    // Step 3: Add a column to extract the ingredient where Number = MaxNumber
    AddPrimaryIngredient = Table.AddColumn(AddMaxNumber, "Primary Ingredient", (row) => 
        let
            records = row[Parsed List],
            maxNum = row[MaxNumber],
            match = List.Select(records, each _[Number] = maxNum)
        in
            if List.Count(match) > 0 then match{0}[Ingredient] else null
    ),

    // Step 4: Remove helper columns
    Cleanup = Table.RemoveColumns(AddPrimaryIngredient, {"Parsed List", "MaxNumber"})
in
    Cleanup

Thanks 

Mason

Click here to download the solution from Onedrive

Click here 

How it works:-

Input this file a csv/text file

13;Apple | 15;Orange | 3;Strawberry
2;Apple | 18;Peach | 3;Melon
27;Blueberry | 7;Blackberry | 1;Lemon | 9;Grape
1;Peach | 11;Banana | 4;Pineapple | 8;Orange | 24;Blackberry

Use a custom delimiter = |  and   specify 5 columns

Add a product id

Click on the product id column.

On the top menu bar select Trasform > Unpivot > Other columns

Remove the empty rows

Extract the Ingredient and Quanity either side of  delimiter

Remove unneed columns and change the Quantity to a number

Create a new table reference the above one
 

Group max quanity by product id

Merge by Product ID and Quantity

Expand the merger to get the Ingredient

Well done. You now have a table with just the max ingredient for each product

Please click thumbs up me taking the time and effort to try help.
Then click [accept solution] if it works.

Thank you.