cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kasiaw29 Frequent Visitor
Frequent Visitor

Retrieving values from a mixed data type column

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!
1 ACCEPTED SOLUTION

Accepted Solutions
AnthonyTilley Established Member
Established Member

Re: Retrieving values from a mixed data type column

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())
datatypes.png
 

View solution in original post

2 REPLIES 2
AnthonyTilley Established Member
Established Member

Re: Retrieving values from a mixed data type column

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())
datatypes.png
 

View solution in original post

kasiaw29 Frequent Visitor
Frequent Visitor

Re: Retrieving values from a mixed data type column

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 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)