DAX: How to split by delimiter, modify one value and then re-merge?

[dvhez]

My data looks like the image below. I want, for example to convert "P.AZUCAR________220" into "P.AZUCAR 220 kV". Ideally to "P. Azucar 220 kV" (space after dot) but the first one will definetly be great.

[MFelix · Accepted solution]

Hi @dvhez,

on the query editor select the column right click then replace values replace the _ by nothing and redo the replace and do the 220 by " 220 kV"

this should give the expected result.

No need to split columns.

Regards,

MFelix

[MFelix · Accepted solution]

Hi @dvhez,

on the query editor select the column right click then replace values replace the _ by nothing and redo the replace and do the 220 by " 220 kV"

this should give the expected result.

No need to split columns.

Regards,

MFelix

[dvhez]

I'm trying to do this in DAX though :(. My data has almost 60 million rows and it takes 1 hour aprox to apply changes to that specific query.

[MFelix · Accepted solution]

Hi @dvhez,

Although your data is always 220 on the end of the column here is a formula to add the last 3 digits and kV to beginnin.

Column = LEFT(Table[Column];FIND("_";Table[Column];1)-1) & " " & RIGHT(Table[Column];3)& " kV" 

Regards,

MFelix