Need to highest Value Based on Digit

[StoryofData]

Hi, 

I need to show value with the lowest number for Case # & Emplpoyee ID

So, almost group by Case # and EmplID to show the value with the lowest value number

Can anyone please help?

[Anonymous · Accepted solution]

Hi @StoryofData 

You can put the following code to Advanced Editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jHUNTQyVtJRMjUzAZFAHJaYU5qq4KIUq4MhbWgEl3fCJm9kAJd3hsgb61qYW+o6guSMDUEmIFQ4QlQY6ZqbGOr6gCQtzYyJVILhDiQlRqYWWJ2CoQTVolgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [case = _t, Empld = _t, #"Value#" = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"case", type text}, {"Empld", Int64.Type}, {"Value#", Int64.Type}, {"Value", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"case", "Empld"}, {{"Count", each _, type table [case=nullable text, Empld=nullable number, #"Value#"=nullable number, Value=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Rank",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Value#", "Value", "Rank"}, {"Value#", "Value", "Rank"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Rank] = 1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Rank"})
in
    #"Removed Columns1"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[StoryofData]

Thank you