Left side of UK Postcode in PowerBI

[RD_PowerBI24]

Hi,

I am trying to extract the left side of the UK Postcode in PowerBI before the space, as per the examploe below of what the output would be in "Area". 

Post Code	Area
G1 0LT	G1
G2 4PP	G2
EH1 1SU	EH1
EH7 4GB	EH7
G82 4AS	G82
EH22 6BD	EH22

In Excel, I've done this before and using cell A2 as a location reference of the postcode, it is this:

LEFT(A2,FIND(" ",A2)-1) to give outputs such as above.

I've tried to do this in PowerBI (N.B. I have 2 postcode columns to merge, hence the IF statement), but with no luck. Can someone point and help me in the right direction:

=LEFT(IF(ISBLANK(All_Sales[POSTCODE]),All_Sales[Postcode 2],All_Sales[POSTCODE]),FIND(" ",IF(ISBLANK(All_Sales[POSTCODE]),All_Sales[Postcode 2],All_Sales[POSTCODE]))-1)

[parry2k · Accepted solution]

@RD_PowerBI24 lets' try this:

LEFT (
    'Table'[All Postcodes],
    FIND ( " ", 'Table'[All Postcodes], 1, LEN ( 'Table'[All Postcodes] ) + 1 ) - 1
)

[RD_PowerBI24]

That worked thank you!

[parry2k]

@RD_PowerBI24 I would recommend creating a sample pbix file with these two columns and sending it over. I think I'm missing something here. 

[RD_PowerBI24]

Hi Parry2k, I don't have the option to do so unfortunately. I'm hoping my example in the first post itself should explain that I just want the left side of a postcode when a gap/space is found inthe postcode.

I use 

= FIND(" ",'Table'[All Postcodes],1,BLANK())

This works and gives me the position of the space. But I now need the left side of everything before the space.

so "BD20 6TD", space is in position 5, and then my desired output is BD20

[parry2k]

@RD_PowerBI24 doesn't make any sense? Can you try with ISBLANK () and see if you get the value? Not sure what it is complaining about, assuming Postcode and PostCode 2 columns are of type text and are in the table.

[RD_PowerBI24]

Hi Parry2k, 

The if statement is picking up a postcode from Postcode 2, if POSTCODE is blank/empty, otherwise picks up a postcode from POSTCODE column. I have 2 sales data sources here, hence the use of

IF(ISBLANK(All_Sales[POSTCODE]),All_Sales[Postcode 2],All_Sales[POSTCODE]) 

Therefore, on this output, I want the left side of the postcode, using the FIND function, where it needs to find a space and then I would use the LEFT function to output all the left part of the postcode before the space.

Does this help?

[parry2k]

@RD_PowerBI24 are you getting errors or incorrect results? Can you make sure All_Sales[POSTCODE] has null (blank) value or is it a space?

[RD_PowerBI24]

Hi Parry2k, I get Error when running the FIND function:

Yes my postcodes do have a space as per the column below: