Forum Discussion

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

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

Anonymous

Not applicable

4 years ago

Hi BM_

You could use Table.SelectRows() function to keep the "true" data. Then use Table.Group() to get the result.

In Power Query Editor=> Home , select Group By , then group by Column [MEMBERID], then add two aggregations . You can get a result like the screenshot below.