Power Query split?

[Misca]

I have a table with a column that has full paths/categories on it:

BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1

OtherBaseCategory/SubCategoryB/SubCategoryB.1

How can I split the paths/categories using Power Query so that I can get all the previous categories as well:

Full Category	Categories
BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1	BaseCategory
BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1	BaseCategory/SubCategoryA
BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1	BaseCategory/SubCategoryA/SubCategoryA.1
BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1	BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1
OtherBaseCategory/SubCategoryB/SubCategoryB.1	OtherBaseCategory
OtherBaseCategory/SubCategoryB/SubCategoryB.1	OtherBaseCategory/SubCategoryB
OtherBaseCategory/SubCategoryB/SubCategoryB.1	OtherBaseCategory/SubCategoryB/SubCategoryB.1

 

[JGordon11]

Not sure how to do that in PQ. I'm interested to see if someone solves that. A VBA UDF can do it fairly easily. In 365 just enter the formula in a cell and press enter. In prior versions you would need to select a one column range long enough to hold the output and then press ctrl-shift-enter.

Function SplitTable(InputRange As Range, Optional Delimiter As String = "/") As Variant
    For Each c In InputRange
        a = Split(c.Value, Delimiter)
        tot = tot + UBound(a) + 1
    Next
    ReDim NewTable(1 To tot, 1 To 1) As Variant
    j = 0
    For Each c In InputRange
        a = Split(c.Value, Delimiter)
        j = j + 1
        NewTable(j, 1) = a(0)
        For i = 1 To UBound(a)
            j = j + 1
            NewTable(j, 1) = NewTable(j - 1, 1) & Delimiter & a(i)
        Next
    Next
    SplitTable = NewTable
End Function

Book1
	A	B	C
1	BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1	BaseCategory
2	OtherBaseCategory/SubCategoryB/SubCategoryB.1	BaseCategory/SubCategoryA
3		BaseCategory/SubCategoryA/SubCategoryA.1
4		BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1
5		OtherBaseCategory
6		OtherBaseCategory/SubCategoryB
7		OtherBaseCategory/SubCategoryB/SubCategoryB.1
8
Sheet1

Cell Formulas
Range	Formula
B1:B7	B1	=SplitTable(A1:A2)
Dynamic array formulas.

 

[Misca]

Thank you for the VBA solution. I guess I could make that work but I'd prefer a Power Query solution.

To me it looks like the solution could be related to the running totals but with a Text.Combine instead of Sum but I just can't make it work.

 

[shaowu459]

工作簿1.xlsx
	A	B	C
1	Full Category
2	BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1
3	OtherBaseCategory/SubCategoryB/SubCategoryB.1
4
5
6	Full Category	Categories
7	BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1	BaseCategory
8	BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1	BaseCategory/SubCategoryA
9	BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1	BaseCategory/SubCategoryA/SubCategoryA.1
10	BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1	BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1
11	OtherBaseCategory/SubCategoryB/SubCategoryB.1	OtherBaseCategory
12	OtherBaseCategory/SubCategoryB/SubCategoryB.1	OtherBaseCategory/SubCategoryB
13	OtherBaseCategory/SubCategoryB/SubCategoryB.1	OtherBaseCategory/SubCategoryB/SubCategoryB.1
14
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="tbl"]}[Content],
    res = Table.ExpandListColumn(
            Table.AddColumn(
                Source,
                "Categories",
                each
                    let
                        b=Text.Split([Full Category],"/")
                    in
                        List.Transform({1..List.Count(b)},(x)=>Text.Combine(List.FirstN(b,x),"/"))),"Categories")
in
    res

 

[shaowu459]

let
    Source = Excel.CurrentWorkbook(){[Name="tbl"]}[Content],
    res = List.Combine(Table.ToList(Source,each List.Transform({0..List.Count(Text.PositionOf(_{0},"/",2))-1},(x)=>Text.BeforeDelimiter(_{0},"/",x))))
in
    res

 

[RoryA]

I was a bit more verbose in my approach:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ReplacementFunction = (InputText)=>
     let
       //Use List.Generate() to do the separation
       DoReplacement = List.Generate(()=>[Counter=List.Count(Text.Split(InputText, "/")), MyText=InputText], each [Counter]>0,
            each [Counter=[Counter]-1, MyText=Text.BeforeDelimiter([MyText], "/", {0, RelativePosition.FromEnd})], each [MyText])
     in
      DoReplacement,
    //Add a calculated column to call the function on every row in the table
    //containing the text to change
    Output = Table.AddColumn(Source, "Changed Text", each ReplacementFunction([Path])),
    #"Expanded Changed Text" = Table.ExpandListColumn(Output, "Changed Text")
in
    #"Expanded Changed Text"

 

[Misca]

Thank you all!

I could've easily used any of the solutions but the ReplacementFunction by RoryA seemed to be the easiest for me to understand & use with my other queries.