Split colum by tekst

[bgroenhuijzen]

Hi,

I would like to know if it is possible to split colums by tekst in power query, in the image below I want to split the row by the tekst "bij opdrachtgever".

So i would like the tekst before "bij opdrachtgever" and the tekst after .

Thanks!

[Smalfly · Accepted solution]

Hi @bgroenhuijzen ,

you can try this for the text before the delimiter:

Table.AddColumn(#"Changed Type", "Text Before Delimiter", each Text.BeforeDelimiter([Voorbeeld], " bij"), type text)

And this for the text after the delimiter:

= Table.AddColumn(#"Inserted Text Before Delimiter", "Custom", each let splitVoorbeeld = Splitter.SplitTextByDelimiter(" opdrachtgever ", QuoteStyle.None)([Voorbeeld]) in Text.Combine({Text.Start(splitVoorbeeld{1}?, 15), Text.Reverse(Text.Middle(Text.Reverse([Voorbeeld]), 35, 1))}), type text)

With "voorbeeld" as the column name I used to create the rule on.

If you ever find yourself in a similar situation again, you can use the "add column from examples" functionality. This allows you to type in the value you want to end up with in a new column. Based on the values entered, you will get a suggestion on how this could be done in power BI:

[Anonymous]

Hi @bgroenhuijzen ,

Was Smalfly or Ronrsnfld's answer helpful? If so, please consider marking the reply as the solution, this will help future searchers of similar questions in the forum to find the answer faster. Thanks in advance!🙂

Best Regards,
Gao
Community Support Team

[ronrsnfld · Accepted solution]

Just enter that string as a custom delimiter in the Split Column dialogue:

[Smalfly · Accepted solution]

Hi @bgroenhuijzen ,

you can try this for the text before the delimiter:

Table.AddColumn(#"Changed Type", "Text Before Delimiter", each Text.BeforeDelimiter([Voorbeeld], " bij"), type text)

And this for the text after the delimiter:

= Table.AddColumn(#"Inserted Text Before Delimiter", "Custom", each let splitVoorbeeld = Splitter.SplitTextByDelimiter(" opdrachtgever ", QuoteStyle.None)([Voorbeeld]) in Text.Combine({Text.Start(splitVoorbeeld{1}?, 15), Text.Reverse(Text.Middle(Text.Reverse([Voorbeeld]), 35, 1))}), type text)

With "voorbeeld" as the column name I used to create the rule on.

If you ever find yourself in a similar situation again, you can use the "add column from examples" functionality. This allows you to type in the value you want to end up with in a new column. Based on the values entered, you will get a suggestion on how this could be done in power BI: