Power Query count occurrences of specific character in column

[Imbizile]

Hello all

Im would like to add a column to a table, that counts the number of occurrences of the specific character | in another column, like this:
CountOccurrencesInColumn.png


As I understand from this post, I should be able to do this in by adding a calculated column with this:

AllCount = LEN(Tabel1[All])-LEN(SUBSTITUTE(Tabel1[All],"|",""))

But I cant figure out how to actually apply that - if I just write it in the Power Query M code it gives errors.

How can I add this column in Power Query?

Please note that I only have up to 2 occurrences in the sample, but in my actual setup I have more than 150 in some rows.

Best regards
Imbizile

[AliGW]

One way:

let
    Kilde = Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content],
    #"Ændret type" = Table.TransformColumnTypes(Kilde,{{"All", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Ændret type", "All", "All - Copy"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","|","",Replacer.ReplaceText,{"All - Copy"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each Text.Length([All])-Text.Length([#"All - Copy"])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All - Copy"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Count"}})
in
    #"Renamed Columns"

[Bo_Ry]

Add Custom column and use this formula

= List.Count(Text.Split([All],"|"))-1

[AliGW]

I really MUST start using the various List commands in PQ!!! Nice one, Bo_Ry.

[Imbizile]

Very nice indeed, Bo_Ry!

Thank you