Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
uhassan
Frequent Visitor

Unable to get the most frequent letter.

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:

 

uhassan_0-1713118632041.png

 

How can I acheive this?

1 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" ?

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

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.

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

 

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" ?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.