Retrieving values from a mixed data type column

[kasiaw29]

Hi all, 

Was wondering if anyone has ever come accross this challange. I've searched all over forums but couldn't find anything speficif on this. 

I have a column in one of my tables that has mixed data types in it text and numbers but it's stored as a text type. I know it's not ideal especially if it's the way it's stored in the database - due to it being a history log which monitors changes within pipeline those being values, statuses, probabilities etc. 

I've tried creting a filtered measure as per below:

Updated Value = CALCULATE(SUMX(FILTER('Pipeline History', 'Pipeline History'[EVENT]="Base Estimated Value"), VALUE('Pipeline History'[NEW_VALUE])))

Which after awhile I've realised that it was wrong as I wasn't even interested in sums at this point. What I needed to do was to get the values from my mixed column and store them as a new column based on the event (type of historical change) and change the data type to make the visual work.

Has anyone done something like this before? 

Thanks!

[AnthonyTilley · Accepted solution]

i have done somthing like this before, not sure if this is the best way but it works well for me.

first i add a calculated colunm that will check the value in the [New Value] field and return the data type based on some checks.

DataType =

-- check if the value is a date, the year function will return an error if the text is not in a date format use this to check if it is a date

var CheckDateType = if(IFERROR(year('Pipeline History'[NEW_VALUE]),BLANK())=BLANK(),false,true)

-- check if the data type is a value, the value fuction will return an error for texts

Var checkValuetype = if(IFERROR(value('Pipeline History'[NEW_VALUE]),BLANK())=BLANK(),false,true)

-- return a value based on the checks above, the date will be true in both because a date is both a date and a value so you must alwasy check for date first

Var Ret = if(CheckDateType = True,"DATE",if(checkValuetype=true, "NUMERIC","TEXT"))

Return Ret

This colunm can be change to add any number of checks for data type for example if a percentage is writen as 10% instead of a decimal 0.1 then this will return as text in the above example. you could add a check to see if the new value containsstrin "%" and if so replace the % with blank and then return the value. 

DataType =

var CheckDateType = if(IFERROR(year('Pipeline History'[NEW_VALUE]),BLANK())=BLANK(),false,true)

Var checkValuetype = if(IFERROR(value('Pipeline History'[NEW_VALUE]),BLANK())=BLANK(),false,true)

var checkPercentage = CONTAINSSTRING('Pipeline History'[NEW_VALUE],"%")

Var Ret = if(CheckDateType = True,"DATE",if(checkValuetype=true, "NUMERIC",if(checkPercentage=true,"PERCENTAGE","TEXT")))

Return Ret

once you have the calculated colunm you cna use this to get the actual values of any data type you wish, for example if you only want the numeric values then add another colunm for numeric values only 

Numeric Values = if('Pipeline History'[DataType]="NUMERIC",value('Pipeline History'[NEW_VALUE]),blank())

or you may whant just the % values

Percentage Values = if('Pipeline History'[DataType]="PERCENTAGE",value(SUBSTITUTE('Pipeline History'[NEW_VALUE],"%",BLANK())),blank())

[kasiaw29]

Hi Tony!

This has actually worked a trick! I knew I couldn't be the only one stuck with the same problem. It's such an unideal situation being stuck with mixed datatypes. 

This is really fabolous! Thank you so much for your help! 

Kasia