cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

View solution in original post

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.

View solution in original post

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 193 members 2,373 guests
Please welcome our newest community members: