Forum Discussion

wskinner's avatar
wskinner
New Member
2 years ago
Solved

Get End Date by Next Action Date - Get Continuous Date Range by Category

Hello, I have a question about generating an end date by only using effective dates. I can easily do this by grouping, and I have also seen how to make the 0 Index column and 1 Index column to mer...
Need Help
  • Ahmedx's avatar
    Ahmedx
    2 years ago

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZS9bsMgFIVfxfKcUi4/BuZ06dQqaqcoA0qsxFJlRzbJ0KcvEONS1TF2JA8gnQ8u59zr7TYHQhkv8lUuKXBM7IJwhAkiGKjdbMqnt/ao6+pbm6qpnzdlZ9rL3lzaMt+ttnkM4gJh5kBuN+9NVzkiWzf1tWw7u/wPSIR5AD4q81Vm65Ouj79HEwWFVwKyn1VKX9O5aU2XmeaeHOB2MMFxJS/a6OzzfNBmpHaBsHIEzCVAInA2ETpFMMY4d8+T7FF72VJ7h5twyl5KCwj2SqcsJu0NchB9JSJpVriA9oHAWClSSqWU02MOTDxkUrioQIAdyCZfEsnneRoKs0751L1Tk0+PCN9Zf80aiy0KY+j10dgi5a0YmSomJnByMAghlFI/UD23OI8AgooMXgISGEvmtdZ7U11toQdPAIAQzlgpHh2vASR9Tuye7WLpIIrZ/7mgpH0+6fYqgHN/tuq7JTmLA2GdFbO6JiJSXbP7AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Position ID" = _t, #"Reports to Position" = _t, #"Effective Date" = _t, #"Workforce Action Reason Description" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Position ID", Int64.Type}, {"Reports to Position", Int64.Type}, {"Effective Date", type date}, {"Workforce Action Reason Description", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Position ID"}),
    #"Grouped Rows" = 
            Table.Group(#"Filled Down", {"Position ID"},
                        {{"Count", (x)=>
                               Table.AddColumn( Table.AddIndexColumn(  x,"Index",0,1),"nextDate", each [
                                a=List.Buffer( x[Effective Date]&{Date.AddDays( List.Max( x[Effective Date]),1)}),
                                b=   List.Range( a,[Index]+1,1){0},
                                c = Date.AddDays(b,-1) ][c]    
)
 

}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Count"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"Position ID", "Reports to Position", "Effective Date", "Workforce Action Reason Description", "Index", "nextDate"}, {"Position ID", "Reports to Position", "Effective Date", "Workforce Action Reason Description", "Index", "nextDate"}),
    #"Grouped Rows1" = Table.Group(#"Expanded Count", {"Position ID", "Reports to Position"}, {{"MinDate", each List.Min([Effective Date]), type nullable date}, {"MaxDate", each List.Max([nextDate]), type nullable date}},GroupKind.Local)
in
    #"Grouped Rows1"

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

Hi wskinner, another solution:

 

Result

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 & "_PrevValue"} 
                    else              {col & "_NextValue"} )),
            d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type any})
        in
            d,

    fnDates = 
        (myTable as table)=>
        [
            // _Detail = GroupedRows{[#"Position ID"=123456]}[All],
            _Detail = myTable,
            _GroupedRowsLocal = Table.Group(_Detail, {"Position ID", "Reports To Position"}, {{"All", each _, type table}, {"Min", each List.Min([Effective Date]), type date}, {"Max", each List.Max([Effective Date]), type date}}, GroupKind.Local),
            _fnShiftMin = fnShift(_GroupedRowsLocal, "Min", -1, null, type date),
            _Ad_EffectiveEndDate = Table.AddColumn(_fnShiftMin, "Effective End Date", each if [Min_NextValue] <> null then Date.AddDays([Min_NextValue], -1) else [Max], type date),
            _RenamedColumn = Table.RenameColumns(_Ad_EffectiveEndDate,{{"Min", "Position#(tab)Effective Date"}}),
            _RemovedOtherColumns = Table.SelectColumns(_RenamedColumn,{"Position ID", "Position#(tab)Effective Date", "Effective End Date"})
        ][_RemovedOtherColumns],

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc/RCcQwDAPQXfJdUCTHvmSW0v3XaN07KOWSP8MTtrzvhbLmUbbSjV51DYIcqrRybDNviGRfsKPPWIORTDC5L9jBdNXF9oFPMhdMgXle3/KSzOwOKJiBeD337w0cT/2Ziz8/Tg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Position ID" = _t, #"Reports To Position" = _t, #"Effective Date" = _t]),
    ChangedTypeUS = Table.TransformColumnTypes(Source,{{"Position ID", Int64.Type}, {"Reports To Position", Int64.Type}, {"Effective Date", type date}}, "en-US"),
    GroupedRows = Table.Group(ChangedTypeUS, {"Position ID"}, {{"All", fnDates, type table}}),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined