Forum Discussion

TAZ95's avatar
TAZ95
New Member
3 years ago
Solved

Power Query Equivalent M code

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

Need Help
  • ImkeF's avatar
    ImkeF
    3 years ago

    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.

2 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    3 years ago

    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.