Forum Discussion

josectps's avatar
josectps
Regular Visitor
2 years ago
Solved

Extract text from column and merge it to a particular format

Hi, In power query what process should I follow so if I introduce the first column with header ¨Sample¨ I would generate the second column with header ¨Result¨? The text that will be my ¨Result...
Need Help
  • josectps's avatar
    josectps
    2 years ago

    This is very helpful. I don't have much experience with Power Query.

     

    Thank you very much guys.

Joe_Barry's avatar
Joe_Barry
Icon for Solution Sage rankSolution Sage
2 years ago

Hi josectps 

 

This might work

You need to change some symbols etc first in the existing column.


  • Highlight the Sample Column and click on Replace Values in the Ribbion
  • The first one to change is the - symbol replace this with #0
  • Repeat and change P3 with P#03
  • Repeat P4 with P#04
  • Repeat 190 QA  with  QA#0190
  • Create a custom column and enter the below code

 

Text.Split([Sample], " ")

 

  •  Extract the values from the list and replace this part of the text " {"Custom", each Text.Combine(List.Transform(_, Text.From)), type text})" with {"Custom", each List.Select(_, each Text.Contains(_, "#")), type list})
  • Extract the values agin in the next step.

This should help.

 

Thanks

Joe