Forum Discussion
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-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.
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"
5 Replies
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"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 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.
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,
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
