Forum Discussion
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!
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
4 Replies
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_backHi 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
