Forum Discussion

makarama's avatar
makarama
Frequent Visitor
3 years ago

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

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      makarama
      Frequent Visitor
      3 years ago

      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.