Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
TAZ95
New Member

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

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

2 REPLIES 2
TAZ95
New Member

Thanks, It did the work

ImkeF
Super User
Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors