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, 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:

UniqueIDStatusIDStageStartDateEndDate
121A1/1/20201/2/2020
131B1/2/20201/3/2020
141A1/3/20201/4/2020
151C1/4/20201/4/2020
162A1/5/20201/6/2020
172A1/6/20201/7/2020
182C1/7/20201/7/2020
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"

     

4 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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's avatar
    lbendlin
    Super 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"

     

  • NikhilChenna's avatar
    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

    • dufoq3's avatar
      dufoq3
      Community Champion
      2 years ago

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