Forum Discussion

mthiru's avatar
mthiru
Frequent Visitor
2 years ago
Solved

End date based on next row (Index+1) for each row

dufoq3 Need help pls ! Continuing from a previous post: Solved: Re: End date based on next row (Index+1) for each ... - Microsoft Fabric Community I am looking to calculate the End date. We need to ...
Need Help
Show and Tell
Tips and Tricks
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi mthiru,

    I've added 1 more row (comfirm if this is expected result or not).

    If there is something wrong, we need to consider sort order. Let me know.

     

    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("i45WMjIwtNA1MNc1MFbSUTI2MTQ0ANKGBiASJGKqFKuDpMgSqyIzFEWG2E0yQTUJU5EJiEXIOpAiIyRFlrqGhlgVGSvFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, MainAccountID = _t, BU = _t, PV = _t, Transaction = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"StartDate", type date}}),
    Ad_PrevWorkingDate = Table.AddColumn(ChangedType, "Prev. Working Date", each List.Max(List.Select(List.Dates(Date.AddDays([StartDate], -1), 3, #duration(-1,0,0,0)), (x)=> Date.DayOfWeek(x, Day.Monday) < 5)), type date),
    GroupedRows = Table.Group(Ad_PrevWorkingDate, {"MainAccountID", "BU", "PV"}, {{"All", each 
        [ a = fnShift(_, "Prev. Working Date", -1 , "PWD Shift"), //Prev. Working Date Shift
          b = fnShift(a, "StartDate", -1 , "SD Shift"), //Prev. Start Date Shift
          c = Table.AddColumn(b, "EndDate", (x)=> if Date.ToText(x[StartDate], [Format="yyyy-MM"]) = Date.ToText(x[SD Shift], [Format="yyyy-MM"]) then x[PWD Shift] else Date.EndOfMonth(x[StartDate]), type date), //Add EndDate
          d = Table.RemoveColumns(c, {"Prev. Working Date", "PWD Shift", "SD Shift"}) //Remove helper columns
        ][d], type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

     

mthiru's avatar
mthiru
Frequent Visitor
2 years ago

dufoq3  Thank you!

I'd be no where without your help on this end date solution. If we group on AccountId, BU and PV then this should work. I have query2 with the above code and am displaying the columns in a table. I add filters to EC and PV. The first two lines should be combined, I will look into grouping that and it should work fine. 

  • dufoq3's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    2 years ago

    You're welcome. Here is updated code - matching with new sample:

     

    let
    fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text, optional _type as type) as table =>
        //v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca), 5. je nepovinny typ
        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"} )),
            d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type any})
        in
            d,

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tdI9C4MwEAbgvxIyG7i7fJm9dBA6dRQHLYEulRIU6r+vGopQYsFA3yEhB3lyHKlr3o+Pzgde8MG/hnk7+ZtfKkxiwQiI5hoiCAB0QqAV8bxdQLcsdisIkgoclGh4U/zjgYM2fdn0yy7JOW0UJvmq7cc2TIz0qsvDOsKaJH72XYi6zdQVaTknqV/acLszUrmNmyV2h54+8z4Oa+1wr+dq7D2TkCnLmKR89c8hfpQyV7cG14E0bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EC = _t, Name = _t, AccountingDate = _t, Ledger = _t, MainAccountId = _t, AcctDescription = _t, BU = _t, PV = _t, Description = _t, Amount = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"AccountingDate", type date}}),
    Ad_PrevWorkingDate = Table.AddColumn(ChangedType, "Prev. Working Date", each List.Max(List.Select(List.Dates(Date.AddDays([AccountingDate], -1), 3, #duration(-1,0,0,0)), (x)=> Date.DayOfWeek(x, Day.Monday) < 5)), type date),
    GroupedRows = Table.Group(Ad_PrevWorkingDate, {"MainAccountId", "BU", "PV"}, {{"All", each 
        [ a = fnShift(_, "Prev. Working Date", -1 , "PWD Shift"), //Prev. Working Date Shift
          b = fnShift(a, "AccountingDate", -1 , "SD Shift"), //Prev. Start Date Shift
          c = Table.AddColumn(b, "EndDate", (x)=> if Date.ToText(x[AccountingDate], [Format="yyyy-MM"]) = Date.ToText(x[SD Shift], [Format="yyyy-MM"]) then x[PWD Shift] else Date.EndOfMonth(x[AccountingDate]), type date), //Add EndDate
          d = Table.RemoveColumns(c, {"Prev. Working Date", "PWD Shift", "SD Shift"}) //Remove helper columns
        ][d], type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll