Remove more than 1 occurrence of Text - Keep the first and remove the rest

[Anonymous]

Hi folks

I have a column with X rows in text format. In this column I have data starting with: Balise: XxxXxxX  which is what I want to be.

However there are lines which have "Balise: XxxXxxX Balise: XxxXxxX Balise: XxxXxxX" I want to remove any occurrence of Balise: XxxXxxX after the first occurrence. 

It is important to notice that! The data is NOT in power query, which means we cannot use the tool duplicate or any code that is otherwise used in power query. 

This data and problem is in Power Bi Desktop. The data is duplicated because I used & to combine 2 columns and thus the places where same data is present it creates the duplicate.

thank you

[Anonymous]

If i understand correctly you want to remove the yellow highlighted text. 

If so; you can use the split column in the edit queries tab to remove the 16 most right characters. Then later remove the extra created column containing the text you wanted to remove.

[Anonymous]

Hi @Anonymous 

That is exactly what I dont want to do.  I cannot use "Edit Query" since the calculation happens at the current page. Therefore the Edit query option is the option before the calculation, thus its not usable.

Any other ways to split the current data?

[v-cherch-msft]

Hi @Anonymous 

You may try below dax to create a new column.

Column = LEFT([Bailser-Alle],LEN([Bailser-Alle])-15)

Regards,

[Anonymous]

@v-cherch-msft 

Since the length of each text in Baliser-Alle is not the same, using Left and giving 15 character to keep is not useful as the texts which is longer than 15 character doesnt appear fully.

The ideal solution will be to use "Balise:" as reference point and say that keep data until second occurrence of "Balise: ".

However I am not sure how it is possible.. I know it initially is about excel coding, which i lack some info about.

[Anonymous · Accepted solution]

Hi, Sorry for the late reply.

I see that i understood your question wrong at the start, im sorry. 

To use DAX to get the right answer you can use the following formulas:

To get the the right Bailse:

RightBailse = RIGHT(SUBSTITUTE(Table1[Bailser-Alle]," ","-"),LEN(SUBSTITUTE(Table1[Bailser-Alle]," ","-"))-SEARCH("-",SUBSTITUTE(Table1[Bailser-Alle]," ","-")))

And for the left part:

LeftBailse = LEFT(SUBSTITUTE(Table1[Bailser-Alle]," ","-"),SEARCH("-",SUBSTITUTE(Table1[Bailser-Alle]," ","-"))-1)

Output:

I hope this helps, goodluck.