Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

 

rankSubgroups.png

View solution in original post

3 REPLIES 3
stvsurf
Resolver I
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"

 

rankSubgroups.png

Anonymous
Not applicable

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

v-juanli-msft
Community Support
Community Support

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors