Differentiating Spaces Within a Code and Between a Code

[NSBS]

Hi Experts,

Is there any way for us to differentiate the SPACES BETWEEN a codename and SPACES WITHIN a codename

Example:

In below Table, initially there is Code List and I want to create a new column to state the position of spaces whether it is in between or within

*Note: each codename will has '@' in it

Code List	Space Position
ABC@ZZ, DEF@ZZ	spaces in between
ABC @zz, DEF@ZZ	spaces within and in between
ABC @zz,DEF@ZZ	spaces within

[Anonymous]

Hi

use the following dax code to add a new column

Column =
VAR code1 =
LEFT (
Codelist_space[Code List],
FIND (
",",
Codelist_space[Code List],
,
0
) - 1
)
VAR code2 =
RIGHT (
Codelist_space[Code List],
LEN ( Codelist_space[Code List] )
- FIND (
",",
Codelist_space[Code List],
,
0
)
)
VAR between =
IF (
FIND (
" ",
code2,
,
0
) = 1,
1
)
VAR within =
IF (
FIND (
" ",
code1,
,
0
) > 0
|| FIND (
" ",
code2,
2,
0
) > 0,
1
)
RETURN
IF (
between = 1
&& within = 1,
"spaces within and in between",
IF (
between = 1,
"spaces in between",
IF (
within = 1,
"spaces within"
)
)
)

If this post helps, Accept it as a solution

[NSBS]

Hi @Anonymous ,

Really appreciate your DAX code.

However my code list is not only limited for 2 codes per row. It might have 1 code or more than 2 codes.

Example: ajk@ZZ, yh W@@ZZ, GWW@ZZ

Is there any way we can differentiate the spaces is WITHIN or BETWEEN code

[Anonymous · Accepted solution]

Hi @NSBS 

Check this dax code for new column

Column =
VAR between =
FIND (
", ",
Codelist_space[Code List],
,
0
)
VAR within =
FIND (
" ",
SUBSTITUTE (
Codelist_space[Code List],
", ",
""
),
,
0
)
RETURN
IF (
between > 0
&& within > 0,
"spaces within and in between",
IF (
between > 0,
"spaces in between",
IF (
within > 0,
"spaces within"
)
)
)

If this post helps, Accept it as a solution

[NSBS]

Hi @Anonymous 

Currently I need the M code for this space detection as well. I already try create it but no output at all.
Kindly help on this matter.

Thank you.

[Anonymous]

hi @NSBS 

You can add a custom column with the code below

let
between = Text.Contains([Code List],", "),
within = Text.Contains(Text.Replace([Code List],", ",",")," ")
in
if between and within then "Space within and in between"
else if between then "Space in between"
else if within then "Space Within"
else "No space"

[NSBS]

Hi @Anonymous ,

Thank you very much for your help. I can detect the spacing now.