Migrate an Excel Formular to count csv delimiters

[dieling]

Hi Community,

I'm quiet new to PBI and try to understand PowerQuery and M
I do spend lot of time and become a master somewhen, but for now it stucks...

What I have:
csv file pipe | delimeted, no head

I did in Excel:
read csv raw to Excel column A - not delimited

count all delimiters of each line item minus 20 divided by 2 in column B
The easy Excel Formular is:

EN =SUM(LEN(RC[-1])-LEN(SUBSTITUTE(RC[-1],""|"",))-20)/2
DE =SUMME(LÄNGE(A1)-LÄNGE(WECHSELN(A1;"|";))-20)/2

-- FillDown

Test Rows for Excel:

a|b|c|d|e|f|g|h|iu|j|k|l|m|n|o|p|q|r|s|t|u|e0|num0|e1|num1|e2|num2|e3|num3|e4|num4|e5|num5|e6|num6|e7|num7

a|b|c|d|e|f|g|h|iu|j|k|l|m|n|o|p|q|r|s|t|u|e0|num0|e1|num1|e2|num2|e3|num3|e4|num4

a|b|c|d|e|f|g|h|iu|j|k|l|m|n|o|p|q|r|s|t|u|e0|num0|e1|num1|e2|num2

a|b|c|d|e|f|g|h|iu|j|k|l|m|n|o|p|q|r|s|t|u|e0|num0|e1|num1

I need a way in Power BI / M to count in the same way as Excel can + the result as a column at the end of the table:

a

b

c

d

e

f

g

h

iu

j

k

l

m

n

o

p

q

r

s

t

u

e0

num0

e1

num1

e2

num2

e3

num3

e4

num4

e5

num5

e6

num6

e7

num7

8

a

b

c

d

e

f

g

h

iu

j

k

l

m

n

o

p

q

r

s

t

u

e0

num0

e1

num1

e2

num2

e3

num3

e4

num4

5

a

b

c

d

e

f

g

h

iu

j

k

l

m

n

o

p

q

r

s

t

u

e0

num0

e1

num1

e2

num2

3

a

b

c

d

e

f

g

h

iu

j

k

l

m

n

o

p

q

r

s

t

u

e0

num0

e1

num1

2

May you have an idea for me
thanks in advance

[dieling]

wow thank you all for your quick responses 🤗
I will try to get your ideas working to solve my issue.
Learning curve is activated 🙂
Will let you know then

[v-jingzhang]

Hi @dieling 

Have you solved this problem? If it is solved, please accept the corresponding helpful reply as Solution to help other people find the solution quickly. If not solved yet, you can also share the problem here so other community users can help it further. 

Best Regards,
Community Support Team _ Jing

[KT_Bsmart2gethe]

Hi @dieling ,

if your intent is to split column by delimiter dynamically: 

Table.SplitColumn(Source, "Test", Splitter.SplitTextByDelimiter("|", QuoteStyle.None))

if you are simply want to count the delimiter:

Table.AddColumn(Source, "Custom", each (List.Count(Text.PositionOfAny([Test],{"|"},Occurrence.All))-20)/2)

Regards

KT

[Vijay_A_Verma]

If you just want a count of delimiters, use following in a custom column where Data is the column

= (List.Count(Text.Split([Data],"|"))-21)/2

If you want both the splitted columns as well as the count - See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tc25FYMwFAXRXhQ7YBXF6CgA87xLBmxlUzzn14Ad3WwmBDcycWZGXLhy41548ORFIvNmYWXjw5eCKnJJFarNGjVmg1qzRZ3Zod7skTc9GszBxdM/dj/KHsy4GHc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Count", each (List.Count(Text.Split([Data],"|"))-21)/2),
    Custom1 = Table.SplitColumn(#"Added Custom", "Data", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), List.Transform({1..List.Max(Table.AddColumn(#"Added Custom", "Temp", each List.Count(Text.Split([Data],"|")))[Temp])},each "Data." & Number.ToText(_)))
in
    Custom1

[rohit_singh]

Hi @dieling ,

Please refer to the solution here :

https://community.powerbi.com/t5/Desktop/How-to-count-a-specify-letter-in-a-string-in-power-query-m/...

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊