Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
i only changed the position of the parentheses
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))
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:
Thank you for your time. Trying to replicate your code i get this
if (LEN(Table1[Column]) = 11, right(Table1[Column],5), CONCATENATE(left(Table1[Column],2),RIGHT(Table1[Column],3)))
is the correct function
With the above code, I can not go to the second step of the if command as shown here:
I am still failing to write down the correct syntax.
Can i kindly ask you to upload your functional Power Bi file?
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)))
@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:
i only changed the position of the parentheses
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))
Yes Nippon is right, the issue is your LEN function needs to return a value before it can be compared to 11.
Thank you both.
My final working code is:
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 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)))
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
69 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
68 |