Distinct count with condition to get unique date only

Hi Jorine ,

You can then create a new blank query in the PowerQuery Editor, copy this code into the Advanced Editor, and refer to these steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k9Ly0xOVdJRMtQ31DcyMDJWitUhU9RI3wiLqLG+MVQ0FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unit Number" = _t, #"Defect Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unit Number", type text}, {"Defect Date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Unit Number", "Defect Date"}, {{"Data", each _},{"MinIndex", each List.Min(_[Index])}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Index"}, {"Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "Distinct Key", each if [Index] = [MinIndex] then 1 else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "MinIndex"})
in
    #"Removed Columns"

Query overview in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum