Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Sachy123
Helper V
Helper V

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.

Sachy123_0-1667580560813.png

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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"

 

AlexisOlson_0-1667688580247.png

 

View solution in original post

5 REPLIES 5
AlexisOlson
Super User
Super User

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"

 

AlexisOlson_0-1667688580247.png

 

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., 

 

Sachy123_0-1667809359567.png

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.

Bifinity_75
Solution Sage
Solution Sage

Hi @Sachy123 , try this:

1º- Duplicate column:

Bifinity_75_0-1667591593129.png

 

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

Bifinity_75_1-1667591694695.png

3º- You already have the first column:

Bifinity_75_2-1667591775809.png

 

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

Bifinity_75_3-1667591905245.png

 

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)

Bifinity_75_4-1667591989418.png

 

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)

Bifinity_75_5-1667592079200.png

 

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)

Bifinity_75_6-1667592171915.png

 

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

 

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

 

10º- Result:

Bifinity_75_7-1667592267831.png

 

I hope it works for you, Best regards

 

 

rsbin
Super User
Super User

@Sachy123 ,

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

rsbin_0-1667589523222.png

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,

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.