Unable to get the most frequent letter.

[uhassan]

I have a column containing letters A, B, and C. The column can have a maximum of 6 letters with combinations of A, B, and C. I want to determine which letter is most frequently repeated. For reference, please find the example below:

How can I acheive this?

[lbendlin]

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WcnR0dHZ2UorViVZycnIGcsBMZyBwhIk6OTo7KsXGAgA=", BinaryEncoding.Base64), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Column = _t]
  ), 
  #"Added Custom" = Table.AddColumn(
    Source, 
    "Most Letter", 
    each Table.Sort(
      Table.Group(
        Table.FromList(Text.ToList([Column])), 
        {"Column1"}, 
        {{"Count", each Table.RowCount(_), Int64.Type}}
      ), 
      {{"Count", Order.Descending}}
    )[Column1]{0}
  )
in
  #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[uhassan]

Hi @lbendlin ,

Thanks for your response,

can you please help me to adjust the source code, when I am trying from my end I am getting multiple error,
here is my source code

let
Source = Excel.Workbook(File.Contents("C:\Users\abc\Desktop\Book1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Input", type text}, {"Output", type text}})
in
#"Changed Type1"


Thanks

[lbendlin · Accepted solution]

let
Source = Excel.Workbook(File.Contents("C:\Users\abc\Desktop\Book1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Sheet1_Sheet", [PromoteAllScalars=true]),
 
  #"Added Custom" = Table.AddColumn(
    #"Promoted Headers", 
    "Most Letter", 
    each Table.Sort(
      Table.Group(
        Table.FromList(Text.ToList([Input])), 
        {"Column1"}, 
        {{"Count", each Table.RowCount(_), Int64.Type}}
      ), 
      {{"Count", Order.Descending}}
    )[Column1]{0}
  )
in
  #"Added Custom"

Which column in that Excel sheet "Sheet1"  contains the text you want to evaluate? "Input" ?