cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vincentwu
Frequent Visitor

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
Community Support
Community Support

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

Hi @vincentwu 

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

vincentwu
Frequent Visitor

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors