Search code examples
stringpowerbinumeric

How can I perform calculations on numeric data that has some text in cells?

I have some data with suppression in it. Sometimes this is for a 'low' value, sometimes to protect anonymity (with a 'c').

Location Value
London 1534
Cardiff 238
Wellington c
Prague low

At the moment I think my options are:

  1. Find and replace these values to 0 or a blank space. Not ideal as a suppressed value might not be that small, and definitely isn't 0.
  2. Remove any row of data with 'low' or 'c' in it. Not ideal as there are quite a lot of suppressions and would leave me with a fairly incomplete dataset.
  3. Is there a third, better option?

If I just keep the data as it is, Power BI rightly makes the data type a string (ABC123) icon. But with this, I can't create meaningful visualisations, as I just get the option to 'count' each field rather than take the value as a numeric.

Solution

  • The best option would be to create a new column that you can use with numbers in it, so you can keep your original data, but have a column to work from In Power Query it would be a custom column:

    if Value.Is(Value.FromText([Value]), type number) then Value.FromText([Value]) else 0

    This will check using Value.FromText is a number. Value.Is will return true/false. So it is a number it will convert the text to a number, if not, in this case I've set it to 0, you can set it to NULL or whatever you logic requires.

    enter image description here

    In DAX you would create a calculated column

    NewValue = IF(ISERROR ( VALUE ( 'Table'[Column1]) ), 0,  VALUE ( 'Table'[Column1]) )

    This will check if the text can be converted to a value, if not it will be 0, otherwise it will turn it to a number

    enter image description here

    Best to do it in Power Query, so it ready for the model to use.