Min date with a condition Power query

[bml123]

Hi,

I have a measure like this

Active_date = CALCULATE(Min(Table[ExtractDate]),Table[Status]="Active")

How do I achieve this in Power query?

[Anonymous · Accepted solution]

Hi @bml123 ,

I used group by and Table.Min to get the dates.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckwuySxLVdJRMjIwMtI10jUFMg2VYnUwZUxATKwyZkCmMVjGMy8RRc5Q1wjINMEhZwhkmuKQMwYyzZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Status = _t, ExtractDate = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Status", type text}, {"ExtractDate", type date}, {"Index", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Status"}, {{"Count", each _, type table [Status=nullable text, ExtractDate=nullable date, Index=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Min([Count],"ExtractDate")),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"ExtractDate"}, {"ExtractDate"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"ExtractDate", "ActiveMinDate"}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Renamed Columns", "Count", {"ExtractDate", "Index"}, {"ExtractDate", "Index"})
in
    #"Expanded Count"

Input:

Output:

Best Regards,

Stephen Tao

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

[amitchandak]

@bml123 , In Power Query you can only create a column not measure

Power Query - List.Min and List.MinN https://youtu.be/9wYdttOPWaQ

You can List.Select https://docs.microsoft.com/en-us/powerquery-m/list-select