Column with different types of Data

[Anonymous]

Hello

I have a table with different data types in a column.

Measure Unit     Value

%	10
Days	11
'000 USD	12
MT	13
$ / MT	14
# incidents	15
Hours	16
'000 LC	17
000 USD	18
'000 $	19
%	20
Days	21
'000 USD	22
MT	23
$ / MT	24
# incidents	25
Hours	26
'000 LC	27
000 USD	28
'000 $	29
%	30
Days	31
'000 USD	32
MT	33
$ / MT	34
# incidents	35
Hours	36
'000 LC	37
000 USD	38
'000 $	39

I was thinking in create a new column with the formatted values.
For values with "%", FORMAT solved my problem.

Formatted Value = IF(Tab1[Measure Unit]="%";FORMAT(Tab1[Valor];"Percent");FORMAT(Tab1[Valor];"Standard"))

But I can't see a solution for others. I tried "Standard"*1000 for the '000 and 000 Measure Units, but didn't work.

Suggestions?

Thanks in advance

[MFelix · Accepted solution]

Hi @Anonymous ,

Try the following code:

Format =
SWITCH (
    Table1[Measure];
    "%"; FORMAT ( Table1[Value] / 100; "Percent" );
    "000 USD"; FORMAT ( Table1[Value] * 1000; "Currency" )
    //Insert additional formats here 
    FORMAT(Table1[Value];"Standard")
)

Depending on your currency you may need to adjust for this for example:

Format =
SWITCH (
    Table1[Measure];
    "%"; FORMAT ( Table1[Value] / 100; "Percent" );
    "000 USD"; FORMAT ( Table1[Value] * 1000; "$0.000" );
    //Insert additional formats here 
    FORMAT(Table1[Value];"Standard")
)

Just keep adding your columns for each type of format.

Insert two lines for each format on the line where is the comment //

Should be as is below.

"%"; FORMAT ( Table1[Value] / 100; "Percent" );
    "000 USD"; FORMAT ( Table1[Value] * 1000; "$0.000" );

Regards,

MFelix