cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
great_AS Member
Member

Translate Excel formula into PowerBI

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

1 ACCEPTED SOLUTION

Accepted Solutions
rafaelmpsantos Established Member
Established Member

Re: Translate Excel formula into PowerBI

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.

2 REPLIES 2
rafaelmpsantos Established Member
Established Member

Re: Translate Excel formula into PowerBI

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.

great_AS Member
Member

Re: Translate Excel formula into PowerBI

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? 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 272 members 2,893 guests
Please welcome our newest community members: