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 Orde...
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

Fowmy's avatar
Fowmy
Icon for Super User rankSuper 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
      Icon for Super User rankSuper 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

        But those columns should be added to the table...I should not come seperately

        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.