Forum Discussion

Sachy123's avatar
Sachy123
Helper V
3 years ago
Solved

split column dynamically with values

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-1005C...
Interesting Links
Need Help
Tips and Tricks
  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

    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"

     

     

Bifinity_75's avatar
Bifinity_75
Solution Sage
3 years ago

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