Forum Discussion

BM_'s avatar
BM_
Frequent Visitor
4 years ago
Solved

Earlier function in M code

Hi,   I have a table with memberships, I want to add a column that always gives the last enddate of a subscription for a specific member.   MEMBERID SubscriptionEndDate LastSubscriptionDate ...
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    BM_  the equivalent M is following

    let
  src=Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "Vcu7CQAxEAPRXhQbtJIX7lyL2f7b8Cdz+piZE0KDgqJDA9UecVzxlk59p8krfUtS+YpM/WczqhY=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [MEMBERID = _t, SubscriptionEndDate = _t]
  ),
  CT = Table.TransformColumnTypes(
    src,
    {{"MEMBERID", Int64.Type}, {"SubscriptionEndDate", type date}}
  ),
  AC = Table.AddColumn(
    CT,
    "LastSubscriptionDate",
    each List.Max(Table.SelectRows(CT, (q) => q[MEMBERID] = [MEMBERID])[SubscriptionEndDate])
  )
in
  AC

     

     

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

BM_  the equivalent M is following

let
  src=Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "Vcu7CQAxEAPRXhQbtJIX7lyL2f7b8Cdz+piZE0KDgqJDA9UecVzxlk59p8krfUtS+YpM/WczqhY=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [MEMBERID = _t, SubscriptionEndDate = _t]
  ),
  CT = Table.TransformColumnTypes(
    src,
    {{"MEMBERID", Int64.Type}, {"SubscriptionEndDate", type date}}
  ),
  AC = Table.AddColumn(
    CT,
    "LastSubscriptionDate",
    each List.Max(Table.SelectRows(CT, (q) => q[MEMBERID] = [MEMBERID])[SubscriptionEndDate])
  )
in
  AC

 

 

NilR's avatar
NilR
Icon for Post Patron rankPost Patron
2 years ago

I really Liked your approach!

I want to rewrite my below DAX Calculated column in M Query but was not sure how to write the Earlier in this funtion, can you help?

 

RANKX ( 
        FILTER (ALL('0410 tbl'),
            '0410 tbl'[GP] = EARLIER('0410 tbl'[GP])
            && '0410 tbl'[ID] = EARLIER('0410 tbl'[ID])
            && '0410 tbl'[DATEDIFF] = EARLIER('0410 tbl'[DATEDIFF])
            && '0410 tbl'[Start_Date] >= _start_date
            && '0410 tbl'[Start_Date] <= _end_date
        ),        
        '0410 tbl'[Start_Date],
        ,
        ASC,
        Dense
    )

 

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    2 years ago

    NilR  my M is not as good as my SQL and /DAX. I can provide you a M but might not be optimized and scalable. Hence, looping

    ++ AlexisOlson ImkeF 

    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      2 years ago

      NilR For this RANKX example, you can do something similar in Power Query by filtering Start_Date, grouping on the GP, ID, and DATEDIFF columns, sorting the result by Start_Date, and then adding an index column.

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi NilR ,
    can you please provide some sample data with the input and desired output data please?

    • NilR's avatar
      NilR
      Icon for Post Patron rankPost Patron
      2 years ago

      I gave up and then added an statement to the sql and created something similar in my sql. Thank you!

      DENSE_RANK() OVER (PARTITION BY.....)