PQ - Power Query split?

[sandy666]

(question from the web (https://www.mrexcel.com/board/threads/power-query-split.1167198/))


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 CategoryResult

BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1BaseCategory

BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1BaseCategory/SubCategoryA

BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1BaseCategory/SubCategoryA/SubCategoryA.1

BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1

OtherBaseCategory/SubCategoryB/SubCategoryB.1OtherBaseCategory

OtherBaseCategory/SubCategoryB/SubCategoryB.1OtherBaseCategory/SubCategoryB

OtherBaseCategory/SubCategoryB/SubCategoryB.1OtherBaseCategory/SubCategoryB/SubCategoryB.1


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

[sandy666]

Here is another option


SourceExpected

Full CategoryResult

BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1BaseCategory

OtherBaseCategory/SubCategoryB/SubCategoryB.1BaseCategory/SubCategoryA

BaseCategory/SubCategoryA/SubCategoryA.1

BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1

OtherBaseCategory

OtherBaseCategory/SubCategoryB

OtherBaseCategory/SubCategoryB/SubCategoryB.1


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