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 there,
I have some data that includes full post code, but I'm trying to find a way to just pull the postal area (either first letter or first 2 letters) into a new column.
I've looked at LEFT functions but couldn't figure out a way to do both.
Any ideas would be very much appreciated.
Solved! Go to Solution.
Hi @Anonymous,
You can also achieve this via creating a calculated column using DAX:
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 ) )
Best regards,
Yuliana Gu
Hi @Anonymous,
Use Split column option (Edit Quries -> Split Column)
Regards,
Siva
This is a great option, but I want to leave the original data in one column, and then split into another, like below.
POSTCODEold POSTCODEnew
DW1 2ZZ DW
A22 2BB A
X33 5ZZ X
PB4 0XX PB
Simply create the duplicate column (Right click on POSTCODEold -> Duplicate Column). Split the column and delete the unwanted column.
That's it
Hi @Anonymous,
If it is working fine, close the thread by accepting the solution.
Thanks,
Siva
Alternative solution:
let Source = Table1, #"Added Custom" = Table.AddColumn(Source, "POSTCODEnew", each List.First(Text.SplitAny([POSTCODEold],"0123456789"))) in #"Added Custom"
Thank you @MarcelBeug
I will try this in a little while and see what happens.
Really appreciate the reply.
Hi @Anonymous,
You can also achieve this via creating a calculated column using DAX:
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 ) )
Best regards,
Yuliana Gu
How can I do this in a measure please? My company don't allow me to create columns in certain datasets we use.
Thanks
Here's another option....
Still, Power Query would be the more appropriate environment for this kind of transformations.
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 |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |