Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

need help with Rank Column

Hello team, need help
I have a Appened-TMR table in power bi, and I have PAX and FREE_PAX columns in that.But In PAX and FREE_PAX have repeated numnbers in those columns.I want create a ranking Order for those ttwo columns For distinct count of values..like if three rows having the values 12,12,12 ,It shoud be gave the rank numberes like 1,2,3 and For another values are also should be started from 1,2,3 like that I want to give the ranks for both columns by adding additional columns to that table. So that I will uese those ranking Orders in DAX functions..How can I achieve this in power bi.

I have created a rank column in Database like below

like same I want to add a column to the table in power Query in PBI for below columns

 

Need Help
Show and Tell
  • Fowmy's avatar
    Fowmy
    2 years ago

    It asks for login, Copy from Excel and paste it here ot share the link after saving in Google Drive

9 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    Anonymous 

    I made a sample dataset to represet your scenario and got the desired results, Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.

     

    let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WMjRUitUhijI2wk0ZmoMpI1N0KhYA", BinaryEncoding.Base64), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [PAX = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"PAX", Int64.Type}}), 
  #"Grouped Rows" = Table.Combine(
    Table.Group(
      #"Changed Type", 
      {"PAX"}, 
      {{"data", each Table.AddIndexColumn(_, "FreePAX", 1, 1, type number)}}
    )[data]
  )
in

     

     


    Basically, you need to step in your table:

    #"Grouped Rows" = Table.Combine(
    Table.Group(
      #"Changed Type", 
      {"PAX"}, 
      {{"data", each Table.AddIndexColumn(_, "FreePAX", 1, 1, type number)}}
    )[data]
  )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Its showing error

      • Fowmy's avatar
        Fowmy
        Super User
        2 years ago

        Anonymous 
        Sorry, after "in", you need to add the last step. I modifed it.

        let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WMjRUitUhijI2wk0ZmoMpI1N0KhYA", BinaryEncoding.Base64), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [PAX = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"PAX", Int64.Type}}), 
  #"Grouped Rows" = Table.Combine(
    Table.Group(
      #"Changed Type", 
      {"PAX"}, 
      {{"data", each Table.AddIndexColumn(_, "FreePAX", 1, 1, type number)}}
    )[data]
  )
in

 #"Grouped Rows"
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    How can add aExcell file