Split column based on a regular expression pattern

[makarama]

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 For	Service	Class	Type of Request	Describe the Request	Request Number	Further Comments
Joe	Coffee	Espresso	Replace Coffee Machine	Coffee Machine needs to be replaced	1213123

[v-yangliu-msft]

Hi @makarama ,

Here are the steps you can follow：

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

Result:

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

3. Result:

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

[makarama]

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.