PQ - Power Query split?

(question from the web)

Quote:

---

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		Result
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

Code:

---

let
//(CC)sandy666
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Full Category"}, {{"Count", each _, type table}}),
    SCount = Table.AddColumn(Group, "SCount", each List.Count(Text.Split([Full Category],"/"))-1),
    List = Table.AddColumn(SCount, "List", each List.Sort({0..[SCount]}, Order.Descending)),
    Expand = Table.ExpandListColumn(List, "List"),
    Subtract = Table.AddColumn(Expand, "Subtraction", each [SCount] - [List], type number),
    TBD = Table.AddColumn(Subtract, "Result", each Text.BeforeDelimiter([Full Category], "/", [Subtraction]), type text),
    TSC = Table.SelectColumns(TBD,{"Result"})
in
    TSC

---

Here is another option

Source		Expected
Full Category		Result
BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1		BaseCategory
OtherBaseCategory/SubCategoryB/SubCategoryB.1		BaseCategory/SubCategoryA
		BaseCategory/SubCategoryA/SubCategoryA.1
		BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1
		OtherBaseCategory
		OtherBaseCategory/SubCategoryB
		OtherBaseCategory/SubCategoryB/SubCategoryB.1

Code:

---

let
//(CC)sandy666
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    SCount = Table.AddColumn(Source, "SCount", each List.Count(Text.Split([Full Category],"/"))-1),
    List = Table.AddColumn(SCount, "List", each List.Sort({0..[SCount]}, Order.Descending)),
    Expand = Table.ExpandListColumn(List, "List"),
    Subtract = Table.AddColumn(Expand, "Subtraction", each [SCount] - [List], type number),
    TBD = Table.AddColumn(Subtract, "Result", each Text.BeforeDelimiter([Full Category], "/", [Subtraction]), type text),
    TSC = Table.SelectColumns(TBD,{"Result"})
in
    TSC

---