Extract Digit Frequency in Excel, Power Query and Python

How many times a digit appears in a number?

Here’s a neat trick:

input - 110901052043051009

Count of Occurrence of zero - 7

Excel:

=LEN(A3)-LEN(SUBSTITUTE(A3,0,))

Power Query:

let

n = 110901052043051009,

Result = Text.Length(Text.Select(Text.From(n),"0"))

in

Result

Python in Excel:

n = xl("A3")

str(n).count('0')

Extract count of each digit in given number:

Excel:

=LET(a,MID(A3,SEQUENCE(LEN(A3)),1),GROUPBY(a,a,ROWS,0,0))

Python in Excel:

from collections import Counter n = xl("A3") [[b,c] for b,c in sorted(Counter(n).items())]

Power Query:

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

L = Text.ToList( Text.From(Table.FirstValue(Source)) ),

Result = Table.FromRows(List.Transform(List.Sort(List.Distinct(L)), (f) => {f, List.Count(List.FindText(L,f))}))

in

Result