cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Fill up by ranking / ranking refreshes every time value changes

 

 

 

Untitled.png

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 ACCEPTED SOLUTION

Accepted Solutions
stvsurf Resolver I
Resolver I

Re: Fill up by ranking / ranking refreshes every time value changes

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"

 

rankSubgroups.png

View solution in original post

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: Fill up by ranking / ranking refreshes every time value changes

Hi @Anonymous 

As tested, i can only rank the "date" column Ascendingly based on the same "Product",

Capture18.JPG

Then create a conditional column

Capture19.JPGCapture20.JPG

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
Resolver I

Re: Fill up by ranking / ranking refreshes every time value changes

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"

 

rankSubgroups.png

View solution in original post

Anonymous
Not applicable

Re: Fill up by ranking / ranking refreshes every time value changes

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 !!!😊

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

Top Solution Authors
Top Kudoed Authors