Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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"
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"
Thank you so much for the answer! it work great but the only thing is the process takes a little bit long because of the size of my table I have to wait some time for the result, but the result comes. Thank you !!!😊
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.