Determining primary value from column

[mryoan04]

Hi gurus,

I'm preparing a dataset in PBI dataflow. I have an Ingredients column like below and wanted to get the Primary Ingredients column that evaluating the quantity of the ingredients and generate the highest quantity ingredients as primary Ingredients. For example, row 1 generates Orange as primary ingredient as it has 15.

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

Could you please help on how to prepare that column in a query?

Thank you very much for your help!

[MasonMA · Accepted solution]

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

[p45cal]

Another, see attached.

In this case I cater for situations when there are equal max ingredients, when all of them are listed.:

Stages:

Add an Index:

Split by the pipe symbol into multiple rows:

Split by semicolon into 2 columns:

Trim both ingredients columns:

Change type of Ingredients.1 to decimal, then group by the Index. All the work is done inside the grouping where the max rows are selected and the Ingredients.2 texts combined:

Then they're sorted on Index (because grouping isn't guaranteed to retain the order) and the Index column removed.

The code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    AI = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    Split1 = Table.ExpandListColumn(Table.TransformColumns(AI, {{"Ingredients", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Ingredients"),
    Split2 = Table.SplitColumn(Split1, "Ingredients", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"Ingredients.1", "Ingredients.2"}),
    Trim = Table.TransformColumns(Split2,{{"Ingredients.1", Text.Trim, type text}, {"Ingredients.2", Text.Trim, type text}}),
    CT = Table.TransformColumnTypes(Trim,{{"Ingredients.1", type number}}),
    Group = Table.Group(CT, {"Index"}, {{"Primary Ingredients", each Text.Combine(Table.SelectRows(_,let max = List.Max(_[Ingredients.1]) in each [Ingredients.1]=max)[Ingredients.2],", ")}}),
    Sort = Table.Sort(Group,{{"Index", Order.Ascending}}),
    CleanUp = Table.RemoveColumns(Sort,{"Index"})
in
    CleanUp

[speedramps · Accepted solution]

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.

[MasonMA · Accepted solution]

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