cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
makarama
Frequent Visitor

Split column based on a regular expression pattern

I have a column where each row follows this format:

 

Requested For = Joe
Service = Coffee
Class = Espresso
Type of Request = Replace Coffee Machine
Describe the Request = Coffee Machine needs to be replaced
Request Number = 1213123
Further Comments =

 

I want to split this into multiple columns as follows (see table below). Do you know how can this be achieved?

I tried splitting by the "=" sign but when I do that it creates columns with values like "Replace Coffee Machine Describe the Request" which is not what I want. Thanks

 

 

Requested ForServiceClassType of RequestDescribe the RequestRequest Number

Further Comments

JoeCoffeeEspressoReplace Coffee MachineCoffee Machine needs to be replaced1213123

 

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

Hi @makarama ,

 

Here are the steps you can follow:

1. In power query – Transform – Split column – Custom  --  =.

vyangliumsft_0-1680509770671.png

Result:

vyangliumsft_1-1680509770672.png

2. Select [Column.1] – Transform – Pivot Column – [Column2] — Don’t Aggregate.

vyangliumsft_0-1680509822949.png

3. Result:

vyangliumsft_1-1680509827600.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Thanks for your time but the input data you used in your example is not what I have. This part:

 

Requested For = Joe
Service = Coffee
Class = Espresso
Type of Request = Replace Coffee Machine
Describe the Request = Coffee Machine needs to be replaced
Request Number = 1213123
Further Comments =

 

is a single string. A single data point in the matrix. It does not span across multiple rows.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors