cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RyanSolArch
Regular Visitor

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

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.

1 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.

files-split.png

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.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

8 REPLIES 8
grandtotal
Frequent Visitor

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:

grandtotal_0-1603754688791.png

 

You can tweak it further in the Query editor.

PhilipTreacy
Super User III
Super User III

Hi @RyanSolArch 

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.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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"

 

 

Hi @RyanSolArch 

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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.

 

RyanSolArch_0-1603756813273.png

 

Thanks @RyanSolArch 

Give me a few minutes to work on it.

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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.

files-split.png

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.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

Thanks so much - this was perfect.

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.