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"

     

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity 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