Create Index Column based on Dates column in Power Query

[dariaglb]

Hi!😉

I have table with dates and empty rows.

I need create Index column, based on dates's hierarchy (1...- earlier and to last date)

Thanks for help in advance!

[Anonymous]

cabn you provide pbix to solve 

[dufoq3 · Accepted solution]

Hi @dariaglb, for future requests - provide sample data in usable format please (not as a screenshot). If you don't know how to do it - read note below my post.

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY/BCcAwCEV38ZzI19gFSgfoPUj336IeAqWpORTEw3s8xN4JUgGhQns9YpOXD4MwlBXaUtsYtrQNHHlYTVsbrWX2H9PBzmvFsHHMc+1tdfpjam38kbbkfgM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [sbj_num = _t, name_id = _t, Date = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}}, "sk-SK"),
    GroupedRows = Table.Group(ChangedType, {"sbj_num"}, {{"All", each 
        [ a = Table.Sort(Table.AddIndexColumn(_, "IndexHelper", 0, 1),{{"Date", Order.Ascending}}),
          b = Table.AddIndexColumn(Table.SelectRows(a, (x)=> x[Date] <> null), "IndexHelper2", 1, 1),
          c = Table.AddColumn(b, "Index", (x)=> if x[Date] <> null then x[IndexHelper2] else null, Int64.Type),
          d = Table.NestedJoin(a, {"IndexHelper"}, c, {"IndexHelper"}, "H", JoinKind.LeftOuter),
          e = Table.ExpandTableColumn(d, "H", {"Index"}, {"Index"}),
          f = Table.RemoveColumns(Table.Sort(e, {{"IndexHelper", Order.Ascending}}), {"IndexHelper"})
        ][f], type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

[dariaglb]

Thanks a ton! It's working.🔥

I am very thankful for your kind help again)

and I appreciate your advice.

[AlienSx]

let
    source = table_with_dates_and_empty_rows,
    sort_index = Table.AddIndexColumn(source, "sort_index"),
    fx = (tbl) => 
        if tbl[Date]{0} = null 
            then tbl 
            else Table.AddIndexColumn(
                Table.Sort(tbl, "Date"), 
                "Index", 
                1, 1
            ),
    gr = Table.Group(
        sort_index, 
        "Date", 
        {"x", fx}, 
        GroupKind.Local, 
        (s, c) => Number.From(List.NonNullCount({s, c}) = 1 )
    )[x],
    combine = Table.Combine(gr),
    sort_back = Table.Sort(combine, "sort_index")[[sbj_num], [name_id], [Date], [Index]]
in
    sort_back