File containing column with multiple Data types.

[abhinav_003]

Hello Friends,
I have an Excel Sheet with details related to "Products" of a footware brand, which contains a Column named 'Size' with different data types like 'Text', 'Integer' and also 'Null values'.

I tried to import this data and converting the "Size" column values to 'Text', this has saved me from the Errors that I was getting earlier on converting Text to Interger, but the problem is now I can't do mathematical opertaions with this numerical data, as now all the values are of Data Type 'Text',

can someone please provide me the solution of this problem, 
I would really appreciate the help!

Thanks a lot in Advance!  🙂

[Ashish_Mathur]

Hi,

What mathematical calculations do you want to do on that column?

[abhinav_003]

Hey Ashish,
Actually in this problem, we have 'Size' column but this happen with any other data column, let say with sales or profit, those are crucial to calculate Monthwise sale or sum of total sales etc. it can be used to draw many insights, that's why I am curious to know the solution of this type to "Multiple Data Type" problems.

We can solve this 'Size' column by Replacing the Text with their respective size numbers, 
For eg: X with 40 or 38 but I wanted the solution of this type of problem as General.

[Ashish_Mathur]

The example yo have taken is incorrect.  To calculate profit/loss, you will only have numbers in a column.  If you can tell me what you wnat to do with that size column (which has entries of mixed data type), then i can possibly help.

[johnyip]

@abhinav_003 , to address your problem,

the answer should be similar to what @v-yangliu-msft offers:

To create a calculated column like the following:

Column =
IF ( ISERROR ( VALUE([Column1]) ), SWITCH([Column1],"X",40,[any other things you needed]),
FORMAT(
VALUE([Column1]),"General Number"))

You can check to see if this works. I didn't test that actually.

[abhinav_003]

Hi there!,
I tried using that but the below error message is reflecting:

Please let me know if I did it wrong.

Thanks & Regards,

Abhinav

[johnyip]

Edit:

The following is even more concise. I will leave my original answer here for your reference.

Calculated_Column =
SWITCH(FORMAT('Table'[Size],"Text"),"L",1,"M",2,"S",3,"XL",4,VALUE([Size])) 

I have actually tried that on PowerBI, this should be working.

 

You should change 1/2/3/4 to the values that you actually needs, and add some other scenarios in SWITCH() to cater to your need.

Original answer:

Calculated_Column =
IFERROR(VALUE([Size]),SWITCH(FORMAT('Table'[Size],"Text"),"L",1,"M",2,"S",3,"XL",4,VALUE([Size])))

[v-yangliu-msft]

Hi  @abhinav_003 ,

Here are the steps you can follow：

1. Create calculated column.

Column =
VAR x = ( [Column1]- 2 )
RETURN
IF ( ISERROR ( x ), [Column1],
FORMAT(
VALUE([Column1]) + 10,"General Number"))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[FreemanZ]

hi @abhinav_003 

you may split the column into two, like:

https://community.powerbi.com/t5/Desktop/Extract-number-and-text-from-string/td-p/1190132

[abhinav_003]

Hi @FreemanZ ,
First of all, thank you for your reply,

Actually I have data like this:

Number in one row and Text in another, spliting them in different columns won't work in this case, I might not be clear in explaining it that time. 
So, what should we do in this situation.