need help with Rank Column

[DanduMani104]

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

[DanduMani104]

How can add aExcell file

[Fowmy]

You can save it in OneDrive, Google Drive, or any other cloud-sharing platform and share the link here.

[DanduMani104]

https://kuonitumlare-my.sharepoint.com/:x:/g/personal/danduma_tumlare_com/EW8hs0jDIflJk46C4P_2FpIBTv...

[Fowmy · Accepted solution]

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

[Fowmy]

@DanduMani104 

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]
  )

[DanduMani104]

Its showing error

[Fowmy]

@DanduMani104 
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"

[DanduMani104]

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.

[Fowmy]

@DanduMani104 

Create a sample table in Excel with the expected result and paste it here in your reply.