Forum Discussion
Anonymous
2 years agoNot applicable
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!
- 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
dufoq3
2 years agoCommunity Champion
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
CombinedAllAnonymous
2 years agoNot applicable
Thanks a ton! It's working.🔥
I am very thankful for your kind help again)
and I appreciate your advice.