cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dieling
New Member

Migrate an Excel Formular to count csv delimiters

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:

abcdefghiujklmnopqrstue0num0e1num1e2num2e3num3e4num4e5num5e6num6e7num7 8
abcdefghiujklmnopqrstue0num0e1num1e2num2e3num3e4num4      5
abcdefghiujklmnopqrstue0num0e1num1e2num2          3
abcdefghiujklmnopqrstue0num0e1num1            2


May you have an idea for me
thanks in advance

5 REPLIES 5
dieling
New Member

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

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
Super User
Super User

Hi @dieling ,

 

if your intent is to split column by delimiter dynamically: 

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

KT_Bsmart2gethe_0-1653402481753.png

 

if you are simply want to count the delimiter:

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

KT_Bsmart2gethe_1-1653402759189.png

 

Regards

KT

Vijay_A_Verma
Super User
Super User

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
Super User
Super User

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! 😊

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10

The Power BI Community Show

Join us on October 3 at 11 am PST when Amit Chandak, a Power BI Super User, will demo how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Kudoed Authors