cancel
Showing results for
Did you mean:

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

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:

How can I acheive this?

1 ACCEPTED SOLUTION
Super User

``````let
Source = Excel.Workbook(File.Contents("C:\Users\abc\Desktop\Book1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

"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

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

3 REPLIES 3
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]
),
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

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.

Frequent Visitor

Hi @lbendlin ,

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}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Input", type text}, {"Output", type text}})
in
#"Changed Type1"

Thanks

Super User

``````let
Source = Excel.Workbook(File.Contents("C:\Users\abc\Desktop\Book1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

"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

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

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Fabric Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors