CODEWITHSUNDEEP
ssaspowerbidaxssas-tabular
uzr
uzr

Reputation: 1220

Remove all non-numeric characters from string DAX

Is it possible to remove all non-numeric characters from a String of varying length in DAX in a calculated column?

The model is an model, so I'm unable to use M language to clean the data at query time.

Also i want to remove starting 0 digits, but not end 0.

Consider the following input in a column:

C1
C2
C3
6F
2F
5Z
05F
A10

And this is the output that i'm looking for

1
2
3
6
2
5
5
10

Upvotes: 2

Views: 10387

Answers (1)

mendosi
mendosi

Reputation: 2051

If the column is known width, and not too wide, you could use this approach. Let's assume that your column of mixed letters and numbers is called [Letters And Numbers]:

Just The Numbers = IFERROR(VALUE(MID([Letters And Numbers], 1, 1)), IFERROR(VALUE(MID([Letters And Numbers], 2, 1)), BLANK()))

Of course, this gets more complicated if you expect more than one number in the column.

In that case, it would be very inconvenient to use DAX language to do it; you'd need to write something like:

Just The Numbers = SUBSTITUTE(SUBSTITUTE([Letters And Numbers], "A", ""), "B", "")

except you'd need 26 substitutes, unless you expect more or fewer non-numeric characters.

Better by far to use M in the edit queries section. Add a custom column with the following definition:

= Table.AddColumn(#"Previous Step", "Just The Numbers", each Text.Combine(List.RemoveItems(Text.ToList([Letters And Numbers]),{"A".."z"})))

And if you're unable to use M because you are using direct query to a SSAS tabular model, then the only option is probably to modify the SQL query that loads the table into the tabular model to add an additional column.

There are probably many examples of some T-SQL that could do that, here is one:

USE AdventureWorksDW2012;

WITH split AS (
    SELECT AddressLine1, v.number, character.c
      FROM DimReseller AS r
      JOIN master..spt_values AS v ON v.number BETWEEN 1 AND LEN(r.AddressLine1)
      CROSS APPLY (VALUES(SUBSTRING(r.AddressLine1, v.number, 1))) AS character(c)
      WHERE v.type = 'P'
        AND character.c LIKE '[0-9]')
SELECT AddressLine1, 
       output = (SELECT c 
                   FROM split
                   WHERE r.AddressLine1 = split.AddressLine1 
                   ORDER BY number ASC 
                   FOR XML PATH, TYPE).value(N'.[1]', N'bigint')
  FROM dbo.DimReseller AS r
  GROUP BY AddressLine1;

Upvotes: 3

Related Questions