Clean up column of unwanted strings using DAX

Chris2016 — Thu, 17 Aug 2023 15:00:58 GMT

Hello,

I have to do a clean up in a very messy column where I have to remove various strings in front or after the text I want to keep.

I have a table like this:

User

123gb233v-dan

46535jdfsfa8v-smit

62773j73662mig_ne_nahd

7266gdv715453ke_haf_te

abb_hdg_hsg.main

abb_hdg_n.abou.hagd

a-ghgd_company

a-has_my_company

cnx_suz-gi

cvg_bhhs_ad

johnb

maxv_2mk

So far, I removed text before a "v-", and kept text that is before a "_my_company" or "_company" string:

_Cleaned User =
var _identify = FIND("v-", 'Table'[User],, blank())
var _length = FIND("v-", 'Table'[User],, LEN('Table'[User]))
var firstclean = SWITCH(TRUE(), NOT(ISBLANK(_identify)),RIGHT('Table'[User], LEN('Table'[User])-_length+1), 'Table'[User])
var _identify2 = FIND("_my_company", firstclean,, blank())
var _length2 = FIND("_my_company", firstclean,, LEN(firstclean))
var secondclean = IF(NOT(ISBLANK(_identify2)), LEFT(firstclean, _length2-1), firstclean)
var _identify3 = FIND("_company", secondclean,, blank())
var _length3 = FIND("_company", secondclean,, LEN(secondclean))
var thirdclean = SWITCH(TRUE(), NOT(ISBLANK(_identify3)),LEFT(secondclean, _length3-1), secondclean)
return thirdclean

Here is the result:

What I need next is a way to remove any string that is longer than 10 characters that contains number values (e.g. from "62773j73662mig_ne_nahd" I want to keep "mig_ne_nahd"). In this condition, LEN('Table'[User])>10 should be included because I have some values that contain numbers, which I want to keep as such (e.g. "maxv_2mk").

Is there any way this can be done?
Also, if you have any suggestions on improving the formula, I'd appreciate the insights.

Thanks!

Re: Clean up column of unwanted strings using DAX

some_bih — Fri, 18 Aug 2023 05:24:05 GMT

Hi Chris2016 for "cleaning" please use Power Query.

Re: Clean up column of unwanted strings using DAX

Chris2016 — Fri, 18 Aug 2023 07:23:57 GMT

Thanks for the reply, I have to do it in DAX.

topic Clean up column of unwanted strings using DAX in DAX Commands and Tips

Clean up column of unwanted strings using DAX

Re: Clean up column of unwanted strings using DAX

Re: Clean up column of unwanted strings using DAX