Forum Discussion

robertosangi's avatar
robertosangi
Helper I
4 years ago
Solved

Index depending on column value

Hi,   I have this situation:   I created a conditional column that indexed my value. My problem is that I want this count restart depends on the week value and more i want to index my value...
Need Help
  • serpiva64's avatar
    serpiva64
    4 years ago

    From your table

    you group rows by week and by count (in order to manage situation of parity)

    hten you group by week

    then you order descending your column count inside your groups

    at this point you add and index column

    at this point you first expand Custom

    you obtain this

    Finally you expand AllRows.1 

    remove unnecessary column and that's done

     

     

     

     

     

     

     

     

     

serpiva64's avatar
serpiva64
Solution Sage
4 years ago

Hi,

From this:

you canobtain this

applying these steps:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc45DoAwDETRu7hOwUSsZ4ko2EFiuX8H2E4xRYqn2NZPSQYJEr9XSh+SjK5WNbmicXY2qoUW1zzaKTenac+fhfLgs2dmrbzo7u2CzT7OSvW3g9pB7eB2UDuoHdwOage3g9vB7aB2cDusvX8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CoUnTerr = _t, Week = _t, Count = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CoUnTerr", type text}, {"Week", Int64.Type}, {"Count", Int64.Type}}),
#"Grouped Rows1" = Table.Group(#"Changed Type", {"Week", "Count"}, {{"AllRows", each _, type table [CoUnTerr=nullable text, Week=nullable number, Count=nullable number]}}),
#"Grouped Rows" = Table.Group(#"Grouped Rows1", {"Week"}, {{"AllRows", each _, type table [CoUnTerr=nullable text, Week=nullable number, Count=nullable number]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.Sort([AllRows],{{"Count", Order.Descending}})),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "Custom", each Table.AddIndexColumn([Custom.1],"Index",1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"AllRows", "Index"}, {"AllRows.1", "Index"}),
#"Expanded AllRows.1" = Table.ExpandTableColumn(#"Expanded Custom", "AllRows.1", {"Count"}, {"Count.1"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded AllRows.1",{"AllRows", "Custom.1"})
in
#"Removed Columns"

 

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

 

 

robertosangi's avatar
robertosangi
Helper I
4 years ago

Hi serpiva64 

Could you please write the steps without code? Is not well understandable but seems work.

 

Thanks

  • serpiva64's avatar
    serpiva64
    Solution Sage
    4 years ago

    From your table

    you group rows by week and by count (in order to manage situation of parity)

    hten you group by week

    then you order descending your column count inside your groups

    at this point you add and index column

    at this point you first expand Custom

    you obtain this

    Finally you expand AllRows.1 

    remove unnecessary column and that's done

     

     

     

     

     

     

     

     

     

    • robertosangi's avatar
      robertosangi
      Helper I
      4 years ago

      Hi  serpiva64 I reached this step:

       

      but expanding i obtained:



      I think I missed something but i'm reaching the goal