cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
airportplanner
Frequent Visitor

IF statement with LEN, LEFT & RIGHT formulas

Hello, new to Power Bi, sorry if this is a basic question.

 

I have a column with 2 types of data (lets call it Flight No Colunn) : BA254/BA879 and BA463/548

 

I have created a helper column that returns the length of each cell (11 or 9)

 

I would like to create an if command like this:

 

IF(LEN[Flight No Column]=11, RIGHT[Flight No Column, 5], CONCATENATE(LEFT([Flight No Column], 2), RIGHT([Flight No Column], 5))

 

However, this does not work as I am combining numbers with text in the same formula.

 

Do you have any suggestions?

 

Thank you very much

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
NipponSahore Resolver II
Resolver II

Re: IF statement with LEN, LEFT & RIGHT formulas

i only changed the position of the parentheses

 2018-06-20_8-53-43.png

Column 2 = IF(LEN(Table1[Flight No Column]) =11, RIGHT(Table1[Flight No Column], 5), CONCATENATE(LEFT(Table1[Flight No Column], 2), RIGHT(Table1[Flight No Column], 3))

View solution in original post

10 REPLIES 10
BKirsch12 Resolver II
Resolver II

Re: IF statement with LEN, LEFT & RIGHT formulas

Can you provide an example of the output you are looking for in both scenarios (LEN 9 and 11). I ran your code, adding the appropriate () and this is the result:

 

CONCAT.PNG

airportplanner
Frequent Visitor

Re: IF statement with LEN, LEFT & RIGHT formulas

Thank you for your time. Trying to replicate your code i get this

 

tttttt.PNG

NipponSahore Resolver II
Resolver II

Re: IF statement with LEN, LEFT & RIGHT formulas

if (LEN(Table1[Column]) = 11, right(Table1[Column],5), CONCATENATE(left(Table1[Column],2),RIGHT(Table1[Column],3)))

is the correct function

airportplanner
Frequent Visitor

Re: IF statement with LEN, LEFT & RIGHT formulas

With the above code, I can not go to the second step of the if command as shown here:

 

tttttttttt.png

 

 

I am still failing to write down the correct syntax.

 

Can i kindly ask you to upload your functional Power Bi file?

NipponSahore Resolver II
Resolver II

Re: IF statement with LEN, LEFT & RIGHT formulas

Can you please share the yellow error message in the screenshot. Also try to copy/paste this :

Column = if (LEN(Table1[Flight No Column]) = 11, right(Table1[Flight No Column],5), CONCATENATE(left(Table1[Flight No Column],2),RIGHT(Table1[Flight No Column],3)))
airportplanner
Frequent Visitor

Re: IF statement with LEN, LEFT & RIGHT formulas

@NipponSahoreunfortunately your code is not working

 

I can not replicate the working example from @BKirsch12

 

I am uploading my file, would you mind taking a look ?

 

https://drive.google.com/open?id=1Tki-_fl_X6HqY2Ea9bV4fV-Je_nxEzQP

 

The error I am getting is this one:

 

error.PNG

BKirsch12 Resolver II
Resolver II

Re: IF statement with LEN, LEFT & RIGHT formulas

The problem is the LEN function. This is what you had in the file you sent:

 

Column 2 = IF(LEN(Table1[Flight No Column] =11), RIGHT(Table1[Flight No Column], 5), CONCATENATE(LEFT(Table1[Flight No Column], 2), RIGHT(Table1[Flight No Column], 3))) 

The length (LEN) function needs to return a value before it can be compared to another number. This is how it should be:  

 

Column 2 = IF(LEN(Table1[Flight No Column]) =11, RIGHT(Table1[Flight No Column], 5), CONCATENATE(LEFT(Table1[Flight No Column], 2), RIGHT(Table1[Flight No Column], 3)))

 

 

NipponSahore Resolver II
Resolver II

Re: IF statement with LEN, LEFT & RIGHT formulas

i only changed the position of the parentheses

 2018-06-20_8-53-43.png

Column 2 = IF(LEN(Table1[Flight No Column]) =11, RIGHT(Table1[Flight No Column], 5), CONCATENATE(LEFT(Table1[Flight No Column], 2), RIGHT(Table1[Flight No Column], 3))

View solution in original post

BKirsch12 Resolver II
Resolver II

Re: IF statement with LEN, LEFT & RIGHT formulas

Yes Nippon is right, the issue is your LEN function needs to return a value before it can be compared to 11.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors