Minimum Value Given Condition

N

nangys

New member
Joined
Jan 4, 2017
Messages
11
Reaction score
0
Points
0
Excel Version(s)
2010
Hi,

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

IDPriceMin Price
A1162162
A1364162
A1174162
A2295149
A2359149
A2149149
A34305
A31245
A355
A31265

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
 
Code: 
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"
 
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!
 
No merge necessary. you just need to go a bit further with Bob's solution:

Code: 
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"
 
Thank you Ken!

I was able to adapt it and it works perfectly. :becky:
 
You must log in or register to reply here.
Back
Top