Forum Discussion

ngct1112's avatar
ngct1112
Icon for Post Patron rankPost Patron
5 years ago
Solved

PowerQuery - Text before multiple Delimiters

Hi,   May I know is there any way I could split text before multiple Delimiters in one function? Original Column Desired Result 1 & 5 1 50 - 1000 50 1000/2000 1000 2000&5000 20...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    Add a step to extract the text before one of those delimiters, and then modify the code in the formula bar to look like this

     

    = Table.TransformColumns(Source, {{"Column1", each Text.Start(_, Text.PositionOfAny(_, {"&", "/", "-"})), type text}})

     

    Pat

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

Add a step to extract the text before one of those delimiters, and then modify the code in the formula bar to look like this

 

= Table.TransformColumns(Source, {{"Column1", each Text.Start(_, Text.PositionOfAny(_, {"&", "/", "-"})), type text}})

 

Pat

Joris_NL's avatar
Joris_NL
Icon for Helper III rankHelper III
2 years ago

In case other people struggle with changing the 'text before delimiter' line into TransformColumns, like I did, here is some clarification: 

 

 

= Table.TransformColumns(#"NameOfPreviousQueryStep", {{"Original column you want to edit", each Text.Start(_, Text.PositionOfAny(_, {"&", "/", "-", "any other symbols"})), type text}})

 

 

So don't expect to create a new column!