M Code help LOOKUP

[SWCNT]

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, Status ID = 1 and each EndDate is the next stage's StartDate (the process went from stage A to B back to A then to C). Row 4's EndDate = StartDate because the next date belongs to StatusID = 2 not 1. I was able to get this to work in DAX with MINX and LOOKUPVALUE, but I can't get the M code to function the same way. Any advice would be greatly appreciated!

Table:

UniqueID	StatusID	Stage	StartDate	EndDate
12	1	A	1/1/2020	1/2/2020
13	1	B	1/2/2020	1/3/2020
14	1	A	1/3/2020	1/4/2020
15	1	C	1/4/2020	1/4/2020
16	2	A	1/5/2020	1/6/2020
17	2	A	1/6/2020	1/7/2020
18	2	C	1/7/2020	1/7/2020

[NikhilChenna]

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

[dufoq3]

Hi, @NikhilChenna, it works but you have to remove this part of your code and then do fill down for [EndDate].

[dufoq3]

Hi @SWCNT,

different approach here. This one should be fast also with bigger dataset.

Result:

let
    fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text) as table =>
        //v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca)
        let
            a = Table.Column(tbl, col),
            b = if shift = 0 or shift = null then a else if shift > 0
                then List.Repeat({null}, shift) & List.RemoveLastN(a, shift)
                else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1),    
            c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) &
                ( if newColName <> null then {newColName} else
                    if shift = 0 then {col & "_Duplicate"} else
                    if shift > 0 then {col & "_PrevtValue"} 
                    else              {col & "_NextValue"} ))
        in
        c,

    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]),
    ChangedType = Table.TransformColumnTypes(Source,{{"UniqueID", Int64.Type}, {"StatusID", Int64.Type}, {"Stage", type text}, {"StartDate", type date}}),
    GroupedRows_Ad_EndDate = Table.Group(ChangedType, {"StatusID"}, {{"All", each Table.FillDown(fnShift(_, "StartDate", -1, "EndDate"), {"EndDate"}), type table}}),
    CombinedAll = Table.Combine(GroupedRows_Ad_EndDate[All])
in
    CombinedAll

[lbendlin · Accepted solution]

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"