Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I would like to break down the UK postcode into an area code. For example, SE27 9HR to SE27.
I found a solution for a DAX command to return the first 2 characters (in this case SE) but I'd like the number as well. This was the solution:
POSTCODEnew = IF ( RIGHT ( LEFT ( 'UK Post Code'[POSTCODEold], 2 ), 1 ) IN { "0", "1", "2", "3", "4", "5", "6", "7", "8", "9" }, LEFT ( 'UK Post Code'[POSTCODEold], 1 ), LEFT ( 'UK Post Code'[POSTCODEold], 2 ) )
Also, some postcodes have 3 characters at the start instead of 4 e.g. E14 0RW. So is there a DAX command to return whatever is in front of the break between the 2 sets of characters.
Solved! Go to Solution.
Hello there @Anonymous ! Wouldn't it be easier for you to do this in Power Query Editor with either the "split column" function or the "extract" function?
Alternatively, you can also try this!
ColumnB =
Var FirstSpace = FIND(" ";'UK Post Code'[POSTCODEold])
Return
LEFT('UK Post Code'[POSTCODEold]; LEN('UK Post Code'[POSTCODEold]) - FirstSpace)
Hope this answer solves your problem! If you need any additional help please tag me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!
Best regards,
Gonçalo Geraldes
Hello there @Anonymous ! Wouldn't it be easier for you to do this in Power Query Editor with either the "split column" function or the "extract" function?
Alternatively, you can also try this!
ColumnB =
Var FirstSpace = FIND(" ";'UK Post Code'[POSTCODEold])
Return
LEFT('UK Post Code'[POSTCODEold]; LEN('UK Post Code'[POSTCODEold]) - FirstSpace)
Hope this answer solves your problem! If you need any additional help please tag me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!
Best regards,
Gonçalo Geraldes
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |