PowerBI replace text query

[JackkG]

Hi Team,

I'm stuck with how to replace text in PowerBI. I got one column which contains multiple names with special chars and would like to get rid of all those and just keep it as names separated with ";". Is there an easy way to achieve this?

example

cell A2
Bage Khiu <57697099.b2wg>;Jonas Mill <59603752.dt98>;Raj Dev <60877919.q2y5>

cell A3
Mac Dillon <57697055.b2wg>;Ben Chad <59644752.dt98>;Matt Pal <60877339.q2y5>


Thank you!

 

[lrobbo314]

Book3
	A	B	C
1	Text		Merged
2	Bage Khiu <57697099.b2wg>;Jonas Mill <59603752.dt98>;Raj Dev <60877919.q2y5>		Bage Khiu;Jonas Mill;Raj Dev
3	Mac Dillon <57697055.b2wg>;Ben Chad <59644752.dt98>;Matt Pal <60877339.q2y5>		Mac Dillon;Ben Chad;Matt Pal
Sheet3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.SplitColumn(Source, "Text", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Text.1", "Text.2", "Text.3"}),
    Extract = Table.TransformColumns(Split, {{"Text.1", each Text.BeforeDelimiter(_, " <"), type text}, {"Text.2", each Text.BeforeDelimiter(_, " <"), type text}, {"Text.3", each Text.BeforeDelimiter(_, " <"), type text}}),
    Merge = Table.CombineColumns(Extract,{"Text.1", "Text.2", "Text.3"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged")
in
    Merge

 

[JackkG]

Wow. This worked perfectly fine.

Thanks a ton lrobbo314!!! That was a great help.

 

[lrobbo314]

Here's a probably better way of doing it. Shorter anyway.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Transform = Table.TransformColumns(Source,{{"Text", each Text.Combine(List.Transform(Text.Split(_,";"), each Text.BeforeDelimiter(_, " <")),";")}})
in
    Transform

 

[JackkG]

Here's a probably better way of doing it. Shorter anyway.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Transform = Table.TransformColumns(Source,{{"Text", each Text.Combine(List.Transform(Text.Split(_,";"), each Text.BeforeDelimiter(_, " <")),";")}})
in
    Transform

Kool. You're genius man!! Codestar!

 

[JGordon11]

A less efficient approach for this particular application uses a generalized custom function I called Table_ColumnRemoveDelimitedText

(tbl as table, col as text, delimiters as list) as table =>
    Table.TransformColumns(tbl,{{col, each let lst = {1..Text.Length(_) - Text.Length(Text.Replace(_,delimiters{0},""))} in 
        List.Accumulate(lst,_, (s,c)=> Text.Replace(Text.Replace(s, Text.BetweenDelimiters(s,delimiters{0},delimiters{1}),""),delimiters{0} & delimiters{1},""))}})

With this function loaded and correctly named the query is simply:

Table_ColumnRemoveDelimitedText(Excel.CurrentWorkbook(){[Name="Table1"]}[Content],"Text",{"<",">"})

Book2
	A	B	C	D
1	Text		Text
2	abc<12345>;def<789>;efgh<345678>		abc;def;efgh
3	abcde		abcde
4	abc<comment1>, And def<comment2>		abc, And def
5	abc; def<comments 3;4>		abc; def
6
Sheet1

or you could put the function in the same query as done here

let 
    myFunction = (tbl as table, col as text, delimiters as list) as table =>
        Table.TransformColumns(tbl,{{col, each let lst = {1..Text.Length(_) - Text.Length(Text.Replace(_,delimiters{0},""))} in 
        List.Accumulate(lst,_, (s,c)=> Text.Replace(Text.Replace(s, Text.BetweenDelimiters(s,delimiters{0},delimiters{1}),""),delimiters{0} & delimiters{1},""))}}),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = myFunction(Source,"Text",{"<",">"})
in 
    Result

Note that reversed, unmatched or nested delimiters may cause unwanted or incomplete transformations.

 

[JackkG]

A less efficient approach for this particular application uses a generalized custom function I called Table_ColumnRemoveDelimitedText

(tbl as table, col as text, delimiters as list) as table =>
    Table.TransformColumns(tbl,{{col, each let lst = {1..Text.Length(_) - Text.Length(Text.Replace(_,delimiters{0},""))} in
        List.Accumulate(lst,_, (s,c)=> Text.Replace(Text.Replace(s, Text.BetweenDelimiters(s,delimiters{0},delimiters{1}),""),delimiters{0} & delimiters{1},""))}})

With this function loaded and correctly named the query is simply:

Table_ColumnRemoveDelimitedText(Excel.CurrentWorkbook(){[Name="Table1"]}[Content],"Text",{"<",">"})

Book2
	A	B	C	D
1	Text		Text
2	abc<12345>;def<789>;efgh<345678>		abc;def;efgh
3	abcde		abcde
4	abc<comment1>, And def<comment2>		abc, And def
5	abc; def<comments 3;4>		abc; def
6
Sheet1

or you could put the function in the same query as done here

let
    myFunction = (tbl as table, col as text, delimiters as list) as table =>
        Table.TransformColumns(tbl,{{col, each let lst = {1..Text.Length(_) - Text.Length(Text.Replace(_,delimiters{0},""))} in
        List.Accumulate(lst,_, (s,c)=> Text.Replace(Text.Replace(s, Text.BetweenDelimiters(s,delimiters{0},delimiters{1}),""),delimiters{0} & delimiters{1},""))}}),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = myFunction(Source,"Text",{"<",">"})
in
    Result

Note that reversed, unmatched or nested delimiters may cause unwanted or incomplete transformations.

Thank you, JGordon11. Appreciate your help on this one. I can see there are different ways to achieve one thing.

​