Minimum Value Given Condition

[nangys]

Hi,

I'm trying to figure out a formula that would give me the minium price given the same item ID. See below:

ID	Price	Min Price
A1	162	162
A1	364	162
A1	174	162
A2	295	149
A2	359	149
A2	149	149
A3	430	5
A3	124	5
A3	5	5
A3	126	5

So far, I can only bring the minimum price in the table with this code:

let
Source = Source,
AddCustom = Table.AddColumn(Source, "Custom", each List.Min(Source[Price]))
in
AddCustom

Thank you for the help

 

[Bob Phillips]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Price", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Min Price", each List.Min([Price]), type number}})
in
    #"Grouped Rows"

 

[nangys]

Hi Bob,

Thank you so much for your reply. This is not exactly what I am looking for since I don't need to group the first column. The final result should look exactly as the example on the original post. Can you advise on how to do that?

However, this gave me the idea that I can create another query using reference, then "Group by" using the ID column and the Min function on column Price, and then do a merge to the original table. That works but if we can skip it then the better.

Thanks!

 

[Ken Puls]

No merge necessary. you just need to go a bit further with Bob's solution:

let    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Price", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Min Price", each List.Min([Price]), type number}, {"All Rows", each _, type table}}),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Price"}, {"Price"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded All Rows",{"ID", "Price", "Min Price"})
in
    #"Reordered Columns"

 

[nangys]

Thank you Ken!

I was able to adapt it and it works perfectly. :becky: