Search code examples
excelpowerbipowerquerypowerpivotm

M Function to trim specific range

I need to trim in Power Query a column that has the following structure:

  • "ABC (XI 011)"
  • "ABC (XI1 02)"

I need to trim/ get every value between "(" and " ". And I need to trim/ get every value between " " and ")". For the 2 examples above the result should be for the first column:

  • XI
  • XI1

And for the second column:

  • 011
  • 02

Is there any chance to get this result with Power Query functions?

Solution

  • Use such code:

    let
    Source = #table({"col"},{{"ABC (XI 011)"}, {"ABC (XI1 02)"}}),
    split = Table.SplitColumn(Source, "col", (x)=>Text.Split(Text.BetweenDelimiters(x, "(", ")")," "))
in
    split