Forum Discussion
Fill up by ranking / ranking refreshes every time value changes
Hey guys, I have a problem with ranking! Basically, I want to add a new column "Rank" addtional to Date and Product.
and everytime the product changes refreshing the rank. And the good thing is I only need to rank "C" prodcut.
My thought is adding a new column if product = C then blank(), and then filldown blanks, but I will need to fill down blanks by ranking. And there isn't really a way to do it.
Hope anyone have the solution for this!!! Help!
1/created an Index column
2/check if the previous product if current product is C and previous is not C => put a 1 in a new column
3/get a running total of the new column
4/group All columns on the running total column
5/Rank the subgroups
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
#"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each if [Index]=1 then 1 else if #"Added Index"{[Index]-2}[Product] = [Product] then 0 else if [Product] = "C" then 1 else 0),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Running Total", each List.Sum(List.Range(#"Changed Type1"[Custom],0,[Index]))),
#"Sorted Rows" = Table.Sort(#"Added Custom",{{"Date", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Running Total"}, {{"group", each _, type table}}),
#"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([group],"Rank",1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom2", "Custom", {"Date", "Product", "Rank"}, {"Date", "Product", "Rank"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded Custom", "RankC", each if [Product] = "C" then [Rank] else 0)
in
#"Added Conditional Column"
3 Replies
v-juanli-msftCommunity Support
Hi Anonymous
As tested, i can only rank the "date" column Ascendingly based on the same "Product",
Then create a conditional column
The whole code in Advanced editor
let //Get data from Excel Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc67CQAhFAXRXl4s6FX3YyiWIfbfhuyCIE56gmF6NwUvH4OKOWs23C8RkiAZckHuJXXJA3khBfItHWnxWtxWYitvNCY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Product = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product", type text}}), //Group by Grouped = Table.Group(#"Changed Type", {"Product"}, {{"AllRows", each _, type table [Date=date, Product=text]}}), //Declare a function that adds a Rank column to a table RankFunction = (tabletorank as table) as table => let SortRows = Table.Sort(tabletorank,{{"Date", Order.Ascending}}), AddIndex = Table.AddIndexColumn(SortRows, "Rank", 1, 1) in AddIndex, //Apply that function to the AllRows column AddedRank = Table.TransformColumns(Grouped, {"AllRows", each RankFunction(_)}), #"Expanded AllRows" = Table.ExpandTableColumn(AddedRank, "AllRows", {"Date", "Product", "Rank"}, {"AllRows.Date", "AllRows.Product", "AllRows.Rank"}), #"Added Conditional Column" = Table.AddColumn(#"Expanded AllRows", "Custom", each if [Product] = "C" then [AllRows.Rank] else [Product]), #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Product", "AllRows.Product", "AllRows.Rank"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Product"}, {"AllRows.Date", "Date"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Product", type text}}) in #"Changed Type1"Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- stvsurf
Resolver I
1/created an Index column
2/check if the previous product if current product is C and previous is not C => put a 1 in a new column
3/get a running total of the new column
4/group All columns on the running total column
5/Rank the subgroups
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
#"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each if [Index]=1 then 1 else if #"Added Index"{[Index]-2}[Product] = [Product] then 0 else if [Product] = "C" then 1 else 0),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Running Total", each List.Sum(List.Range(#"Changed Type1"[Custom],0,[Index]))),
#"Sorted Rows" = Table.Sort(#"Added Custom",{{"Date", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Running Total"}, {{"group", each _, type table}}),
#"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([group],"Rank",1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom2", "Custom", {"Date", "Product", "Rank"}, {"Date", "Product", "Rank"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded Custom", "RankC", each if [Product] = "C" then [Rank] else 0)
in
#"Added Conditional Column"