Unable to change data type in calculated column

[Anonymous]

Hi Everyone,

I am adding an addtional column in my table in data window and performing an if statement.

Statement : Column=IF(Table[Distance]=0,"-",100-(100*Table[Reversing Upon Vehicle Start Up]/Table[COUNT]))

I also want to add more column with different formulae like 

Cornering = IF(Table[Distance]=0,"=",MAX(100-((Table[Count]*1609.34)/Table[Distance]),0))

I am unable to get "-" when I am running the statement reason is my output would be a numeric number so I want to change my data type as Any so that I can perform the analysis.

Note: I don't want to change the type as String becuse I want to pefrom some mathematical calulcations also want to add other columns consisting Max,Min conditions. Is there any if I can add a custom column in query editor and can apply some If, Max, Min condition in column and change data type as "Any"

Please see the below table and expected column output

Distance	Count	Column
0	6	-
0	8	-
0	13	-
0	15	-
0	11	-
1	7	0.142857
1	13	0.076923
21	14	1.5
16	12	1.333333
25	16	1.5625
25	15	1.666667
32	17	1.882353
4	8	0.5
31	4	7.75
39	20	1.95
39	8	4.875
5	5	1
33	13	2.538462
13	20	0.65
20	11	1.818182
40	5	8
24	20	1.2

Distance	Count	Column
0	6	-
0	8	-
0	13	-
0	15	-
0	11	-
1	7	0.142857
1	13	0.076923
21	14	1.5
16	12	1.333333
25	16	1.5625
25	15	1.666667
32	17	1.882353
4	8	0.5
31	4	7.75
39	20	1.95
39	8	4.875
5	5	1
33	13	2.538462
13	20	0.65
20	11	1.818182
40	5	8
24	20	1.2

Kindly help

Regards

Uphar Tandon

[harshnathani]

Hi @Anonymous ,

Apart from what Ibendin has mentioned, the formula for MAX is wlso incorrect. 

MAX takes in Column as an input while you have given mathematical expression in the function.

Cornering =

var a = 'Table'[Count]*1609.34

var b = DIVIDE(a, 'Table'[Distance])

var c = 100 - b

RETURN

IF('Table'[Distance]=0,"=", CONVERT(c,STRING))

You can either convert the column into a string and then convert it back into integer when you want to do the MIN,MAX calculation.

Regards,

Harsh Nathani

[lbendlin · Accepted solution]

Why would string prevent you from doing your mathematical calculations?  Store as string, and then when you do want to do your calculation (and you are confident the stored string is actually a number and not just a "-") you convert the value programmatically to number.

Or, you know, you could use BLANK() instead of "-", and not have these issues at all.

[Anonymous]

Thanks for the suggestion. Its working 🙂