Translate Excel formula into PowerBI

[Anonymous]

Hello folks!

I want to convert the excel formula of the file attached into Power BI.

I tried with the following expression but didn't work out (it returned me "Expressions that yield variant data-type cannot be used to define calculated columns."):

Ct. Converted TEST = 
            IF (
                SALES[Ct. Pietra Principale Unitaria - Copy] < 1;
                 SALES[Ct. Pietra Principale Unitaria - Copy];
                IF (
                    SALES[Ct. Pietra Principale Unitaria - Copy]> 9000;
                    LEFT ( SALES[Ct. Pietra Principale Unitaria - Copy]; 1 ) & ","
                        & MID ( SALES[Ct. Pietra Principale Unitaria - Copy]; 2; 5 )
                )
            )

Just to be clear i need both formulas of the green columns into Power BI. The second one is an "approximate vlookup".

Thanks to all.

Cheers,

AS

[rafaelmpsantos · Accepted solution]

Try it

Ct. Converted TESt =
Var ctpietraprincipale = VALUE(SALES[Ct. Pietra Principale Unitaria])
var secondaespressione = VALUE(LEFT(VALUE(ctpietraprincipale);1) & "," & MID(VALUE(ctpietraprincipale);3;5))
return
if (
ctpietraprincipale<1;
ctpietraprincipale;
if( ctpietraprincipale>9000;
secondaespressione;0
)
)

If it solve your issue mark as solved, and please give me your positive feedback... bye.

[Anonymous]

Hello!

It did workout, however you had to substitute "," with "."

Anyhow very happy it works now. Any idea on how to get the second green column?