Forum Discussion
sbadiger3
Helper I
Helper IHow to rank a row based on a column
Hi everyone, I need help in creating a column in a table in power query. In my table I have a column 'order_line_id'. This column has duplicates. Now, I need to create a column 'rank' which gives ran...
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1U4rVgTLNEUwjCNPU3NTSGCEKVBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [order_line_id = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"order_line_id", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"order_line_id"}, {{"Count", each _, type table [order_line_id=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Rank", 1,1)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Rank"}, {"Rank"}), #"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Rank", Order.Ascending}}) in #"Sorted Rows"
mussaenda
Community Champion
Community Champion
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1U4rVgTLNEUwjCNPU3NTSGCEKVBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [order_line_id = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"order_line_id", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"order_line_id"}, {{"Count", each _, type table [order_line_id=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Rank", 1,1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Rank"}, {"Rank"}),
#"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Rank", Order.Ascending}})
in
#"Sorted Rows"