Power Query Equivalent M code

[TAZ95]

Hello, I have Dataset that keeps track of Client purchases. This DAX code will return 1 for the first time the client occurs in the data. So if the client purchased multiple items in different days. It will return 1 for the first date and 0 for the rest. 

Column = IF (
    CALCULATE (
        COUNTROWS ( Sales ),
        FILTER (
            ALLEXCEPT ( Sales, Sales[client_id] ),
            Sales[date_purchase] <= EARLIER ( Sales[date_purchase]  )
        )
    )
        > 1,
    0,
    1
)

I want to find the same result using Power Query M code. Or find the equivalent M code of this DAX.

Thank you

Regards

[TAZ95]

Thanks, It did the work

[ImkeF · Accepted solution]

Hello @TAZ95 ,
to get a decent performance here in Power Query, I'd recommend to group the table on client_id first and perform a sort/add index on those partitions:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUNdQ1MjA2VorVgQkYAQWMjJAEjBECRtgFjNAFDKECsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [client_id = _t, date_purchase = _t]),
    #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"client_id", Int64.Type}, {"date_purchase", type date}}
  ), 
  #"Grouped Rows" = Table.Group(
    #"Changed Type", 
    {"client_id"}, 
    {{"All", each _, type table [client_id = nullable number, date_purchase = nullable date]}}
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Grouped Rows", 
    "Custom", 
    each Table.AddIndexColumn(
      Table.Sort([All], {{"date_purchase", Order.Ascending}}), 
      "Index", 
      1, 
      1
    )
  ), 
  #"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"All"}), 
  #"Expanded Custom" = Table.ExpandTableColumn(
    #"Removed Columns", 
    "Custom", 
    {"date_purchase", "Index"}, 
    {"date_purchase", "Index"}
  ), 
  #"Replaced Value" = Table.ReplaceValue(
    #"Expanded Custom", 
    each [Index], 
    each if [Index] > 1 then 0 else 1, 
    Replacer.ReplaceValue, 
    {"Index"}
  )
in
  #"Replaced Value"

Please paste this code into the advanced editor of a new blank query, replacing all the existing default values in there. Then follow the steps.