Forum Discussion

SWCNT's avatar
SWCNT
New Member
2 years ago
Solved

M Code help LOOKUP

In my sample table below I'm trying to get the M code to work for generating EndDate. EndDate = the StartDate of the next Stage while the StatusID is the same. For example, in the first 4 rows, Stat...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRS0lEyBGJHEK1vqG9kYGSgFKsDlDGGyjiBZYyQZExQ9BgjyZhCZZzBMiZIMmZAESO4HlMkGXMUGTMkGQuoDMQ0c6hMLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UniqueID = _t, StatusID = _t, Stage = _t, StartDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UniqueID", Int64.Type}, {"StatusID", Int64.Type}, {"Stage", type text}, {"StartDate", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "EndDate", each try if [StatusID]=#"Changed Type"{[UniqueID]-11}[StatusID] then #"Changed Type"{[UniqueID]-11}[StartDate] else [StartDate] otherwise [StartDate])
in
    #"Added Custom"

     

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRS0lEyBGJHEK1vqG9kYGSgFKsDlDGGyjiBZYyQZExQ9BgjyZhCZZzBMiZIMmZAESO4HlMkGXMUGTMkGQuoDMQ0c6hMLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UniqueID = _t, StatusID = _t, Stage = _t, StartDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UniqueID", Int64.Type}, {"StatusID", Int64.Type}, {"Stage", type text}, {"StartDate", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "EndDate", each try if [StatusID]=#"Changed Type"{[UniqueID]-11}[StatusID] then #"Changed Type"{[UniqueID]-11}[StartDate] else [StartDate] otherwise [StartDate])
in
    #"Added Custom"