Forum Discussion

dieling's avatar
dieling
New Member
4 years ago

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, n...
Need Help
Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

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