Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Create Index Column based on Dates column in Power Query

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!
  • dufoq3's avatar
    2 years ago

    Hi Anonymous, 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