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