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

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"

NikhilChenna

Skilled Sharer

2 years ago

Hi SWCNT ,

This below M code should help you,

/ Add a custom column for EndDate AddedCustom = Table.AddColumn(SortedTable, "EndDate", each let currentID = [UniqueID], currentStatusID = [StatusID], nextRow = Table.SelectRows(SortedTable, each [UniqueID] > currentID and [StatusID] = currentStatusID, 1), endDate = if Table.RowCount(nextRow) > 0 then nextRow{0}[StartDate] else null in endDate, type date)

Regards,

Nikhil Chenna

if your issue is solved give a thumbsup