How to split column by 4th instance of character from the left?

[Anonymous]

We have UNC path data and need to split it after the 3rd, 4th, and 5th instance of the "\" separting the folders, to extract the two top high level folders, but none of the built in functions seem to be able to do this.

e.g.

\\domain.com.au\fs01\Retail\etc

\\domain.com.au\fs03\Sales\etc

\\domain.com.au\fs31\Factory\etc

\\domain.com.au\fs12\HR\etc

In the above example, we want to separate the columns into 3, first break out the less important \\domain.com.au\FSxx\, then a column for "Retail, Sales, Factory, HR", and then the third/final being the sub folders.

Am I missing an obvious trick here?  Presumably so... but I spent a while and it didn't appear obvious, sorry!  Many thanks in advance.

[grandtotal]

You can use the Split Column and Merge column together like this:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WigGClPzcxMw8veT8XL3E0piYtGIDw5iYoNSSxMycmJjUkmSlWB1cCo1jYoITc1KLCagzBhrolphckl9USUCloVFMjEcQVFEsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("\", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"}),
#"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter",{"Column1.1", "Column1.2", "Column1.3", "Column1.4"},Combiner.CombineTextByDelimiter("\", QuoteStyle.None),"Merged"),
#"Added Suffix" = Table.TransformColumns(#"Merged Columns", {{"Merged", each _ & "\", type text}})
in
#"Added Suffix"

Final result:

You can tweak it further in the Query editor.

[PhilipTreacy]

Hi @Anonymous 

Loading that data from a table called UNC, use this in Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="UNC"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UNC", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "UNC", Splitter.SplitTextByDelimiter("\", QuoteStyle.Csv), {"UNC.1", "UNC.2", "UNC.3", "UNC.4", "UNC.5", "UNC.6"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"UNC.1", type text}, {"UNC.2", type text}, {"UNC.3", type text}, {"UNC.4", type text}, {"UNC.5", type text}, {"UNC.6", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"UNC.1", "UNC.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"UNC.3", "UNC.4"},Combiner.CombineTextByDelimiter("\", QuoteStyle.None),"Host"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"UNC.5", "Sub1"}, {"UNC.6", "Sub2"}})
in
    #"Renamed Columns"

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Anonymous]

Thanks Phil, and Grandtotal.

It boggles my mind how quickly you came up with that... I'm afraid I have a follow up question, this is my query below, I'm not sure how to fit that code you've provided into my existing query.  Is this easy to do?

Also please note:  There are very long paths in here, I don't want 23 columns... so the far right remaining path length data should all go into a single column.

let
Source = Folder.Files("C:\blah\CombinedReports"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Repository", type text}, {"File Name", type text}, {"Status", type text}, {"Comment", type text}, {"Current Label", type any}, {"Current Label ID", type any}, {"Applied Label", type any}, {"Applied Label ID", type any}, {"Condition Name", type any}, {"Matched String", type any}, {"Information Type Name", type any}, {"Matched Information Types String", type any}, {"Action", type any}, {"Last Modified", type datetime}, {"Last Modified By", type any}, {"Protection Before Action", type any}, {"Protection After Action", type any}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","","File Excluded",Replacer.ReplaceValue,{"Action"}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([Repository] <> "\\AUUXXX011.XX.XX.com.au\CLU-SMB1\Test\"))
in
#"Filtered Rows"

[PhilipTreacy]

Hi @Anonymous 

No worries.  Can you please share a source file and PBIX/Excel workbook so I can run your query against it.  I can see you have a lot of columns in your actual data so at this point I'm guessing as to which one contains the UNC paths.

Phil

[Anonymous]

Apprecate it - unfortunately the data set is huge (2gb - can't even upload it!  but it's manageable/doing the job), an additionally the content is all quite sensitive.  Will this do?  There are heaps of other columns out to the right you are correct, but they don't need changing here.

[PhilipTreacy]

Thanks @Anonymous 

Give me a few minutes to work on it.

Phil

[PhilipTreacy · Accepted solution]

Hello @RyanSolArch

Here's your query with the new steps added after "Filtered Rows"

let

Source = Folder.Files("C:\blah\CombinedReports"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[ Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Repository", type text}, {"File Name", type text}, {"Status", type text}, {"Comment", type text}, {"Current Label", type any}, {"Current Label ID", type any}, {"Applied Label", type any}, {"Applied Label ID", type any}, {"Condition Name", type any}, {"Matched String", type any}, {"Information Type Name", type any}, {"Matched Information Types String", type any}, {"Action", type any}, {"Last Modified", type datetime}, {"Last Modified By", type any}, {"Protection Before Action", type any}, {"Protection After Action", type any}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","","File Excluded",Replacer.ReplaceValue,{"Action"}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([Repository] <> "\\AUUXXX011.XX.XX.com.au\CLU-SMB1\Test\")),

// New Steps From Here
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "FilePath", each Text.AfterDelimiter([File Name],"\",4)),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "File Name", Splitter.SplitTextByDelimiter("\", QuoteStyle.Csv), {"UNC.1", "UNC.2", "UNC.3", "UNC.4", "UNC.5"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"UNC.1", "UNC.2"}),
#"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"UNC.3", "UNC.4"},Combiner.CombineTextByDelimiter("\", QuoteStyle.None),"Merged"),
#"Added Slashes" = Table.TransformColumns(#"Merged Columns", {{"Merged", each "\\" & _ , type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Added Slashes",{{"UNC.5", "Folder"}})

in
    #"Renamed Columns"

I've recreated a subset of your data to test it. You'll end up with something like this.

You'll need to drag the FilePath folder wherever you want. And rename the new columns to whatever you want.

I wasn't sure if I wanted forward slashes before or after the UNC path and folders. That's easy to solve if you want it changed.

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking Thumbs Up.

[Anonymous]

Thanks so much - this was perfect.