Forum Discussion

koorosh's avatar
koorosh
Post Partisan
2 years ago
Solved

count back slash

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?

Need Help

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    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"
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    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's avatar
      koorosh
      Post Partisan
      2 years ago

      Hi Grep, please check the above result!