split column dynamically with values

[Sachy123]

My data is as below in a hierarchy format as shown below as a path.

Path

1CO-11C-1000C1

1CO-100S-1001C1

1CO-11C-101S-1002C1

2CO-12C-104S-1003C1

2CO-12C-105S-1004C1

1CO-10C-1005C1

1CO-10C-102S-1006C1

1CO-10C-103S-1007C1

1CO-11C-100S

1CO-11C-101S

1CO-10C-102S

1CO-10C-103S

2CO-12C-104S

2CO-12C-105S

1CO-10C

1CO-11C

2CO-12C

2CO-13C

1CO

2CO

The level in the hierarchy is also dynamic. E.g. it can go to level 5 or level 1

So basically I want to be able to create dynamic column based on the values 

and place the values from the column in the right column.

[AlexisOlson · Accepted solution]

1. Split the text into a list using the delimiter "-".

Text.Split([Path], "-")

"1CO-11C-1000C1" --> {"1CO", "11C", "1000C1"}

2. Split each element of the list by the transition from digits to letters.

List.Transform(PrevStep, each Splitter.SplitTextByCharacterTransition({"0".."9"}, {"A".."Z"})(_))

{"1CO", "11C", "1000C1"} --> {{"1","CO"},{"11","C"},{"1000","C1"}}

3. Zip this list of 3 lists with 2 elements to get a list of 2 lists with 3 elements as follows:

List.Zip(PrevStep)

{{"1","CO"},{"11","C"},{"1000","C1"}} --> {{"1","11","1000"},{"CO","C","C1"}}

 4. Convert this pair of lists into a record.

Record.FromList(PrevStep{0}, PrevStep{1})

{{"1","11","1000"},{"CO","C","C1"}} --> [C0="1", C="11", C1="1000"]

 5. Expand the record column.

Put all together:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc6xDcAgDATAXaiDZBtIBmCAFJSI/ddAvEji6BuETm/7ew9a76hao4pI1TCOTSJtPeoNMYXbdltuyzM8kRd4/u3GuUJkiJ7kCX5RFWnUjTbSKqpNff2IP+CD3z+9mcfCGBM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Path = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each let pairs = List.Zip(List.Transform(Text.Split([Path], "-"), each Splitter.SplitTextByCharacterTransition({"0".."9"}, {"A".."Z"})(_))) in Record.FromList(pairs{0}, pairs{1}), type record),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"CO", "C", "C1", "S"}, {"CO", "C", "C1", "S"})
in
    #"Expanded Custom"

[Sachy123]

what if my data has got more levels of hierarchy but the name of hierarchy is the same?

is there a way to split columns like e.g CO1 , CO2 etc., 

Path

1CO-11C-1000C1

1CO-100CO-1001C1

1CO-11C-101S-1002C1

2CO-12C-104S-1003C1

2CO-12C-105S-1004C1

1CO-10C-1005C1

1CO-10C-102C-1006C1

1CO-10C-103S-1007C1

1CO-11C-100S

1CO-11C-101S

1CO-10C-102S

1CO-10C-103S

2CO-12C-104S

2CO-12C-105S

1CO-10C

1CO-11C

2CO-12C

2CO-13C

1CO

2CO

[AlexisOlson]

The trick would be to make what I called pairs{1} a list of distinct values by incrementing repeated ones.

This is certainly possible but probably deserves a separate post as I can't think of a quick single-step solution.

[Bifinity_75]

Hi @Sachy123 , try this:

1º- Duplicate column:

2º.- Split duplicate column by delimiter "-"

3º- You already have the first column:

4º- The "C" is repeated, replace the value "C1" with "XX" in the Column "Path - Copia 2":

5º- Create a Custom column with this formula for the column "C":

if Text.PositionOf([#"Path - Copia.2"],"C",Occurrence.Last)<0 then ""
else
Text.Range([#"Path - Copia.2"],Text.PositionOf([#"Path - Copia.2"],"C",Occurrence.Last)-2,3)

6º.- Create a Custom column with this formula for the column "S":

if Text.PositionOf([#"Path - Copia.2"],"S",Occurrence.Last)<0 then ""
else
Text.Range([#"Path - Copia.2"],Text.PositionOf([#"Path - Copia.2"],"S",Occurrence.Last)-3,4)

7º- Create a Custom column with this formula for the column "C1":

if Text.PositionOf([#"Path - Copia.2"],"XX",Occurrence.Last)<0 then ""
else
Text.Range([#"Path - Copia.2"],Text.PositionOf([#"Path - Copia.2"],"XX",Occurrence.Last)-4,5)

8º- Replace value "X" for "C1" in column

9º- Sort the columns and delete the duplicate column "Path - Copia 2"

10º- Result:

I hope it works for you, Best regards

[rsbin]

@Sachy123 ,

First step is to split your [Path] column by Delimiter using "-":

From there, you can then start manipulating each column as you see fit. 

The first column you just need to rename to "CO".  You can use various other if functions and text functions to ensure each Column has the correct category.  I hope I have given you enough to go on.  If you need further help as you progress, please reach out.

Regards,