count back slash

[koorosh]

Hi Everybody, I have a table with a column "folder path". It has values like the following:(examples)

C:\koorosh\apple\documents

C:\mac\user

C:\iphone\data\package\app

I want to find the number of subfolder that the file is located. It seems one solution is to count the back slash but I got an error when adding custom column with the following code: List.Count(Text.split([folder path]),"\")) What is the solution, please?

[ThxAlot · Accepted solution]

Easy enough,

[Greg_Deckler]

@koorosh Also, your orginal formula has 2 syntax errors in it, here is the correct version:

List.Count(Text.Split([folder path],"\"))

M is case sensitive so Text.Split, not Text.split and you ended your Text.Split early with an errant )

[koorosh]

Hi Grep, please check the above result!

[Greg_Deckler]

@koorosh Simple fix, just subtract 1:

List.Count(Text.Split([Column1],"\")) - 1

[koorosh]

Thanks Greg. I knew simple fix . I just let you know the result

[Greg_Deckler]

@koorosh You should be able to easily collapse this down to a single column:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PcxBDoAgDAXRu7D2BG49Br+LBhsxCG0o3N+oidvJy8QYthUoql09A2x2CbBrmlXa8EDLJyonYLr0v5yWtT2WBwPGqfAh7yEQ3Q==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","\","",Replacer.ReplaceText,{"Column1 - Copy"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each Text.Length([Column1]) - Text.Length([#"Column1 - Copy"]))
in
    #"Added Custom"