The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I have a column which has full addresses in it which due to the various ways the addresses have been handed over from the client I can't use the extract by delimiter function. I would like to create a new column which extracts the first 4 digits from the postcode. I have provided a screenshot of what it should look like. Can anyone help me please?
Thanks,
David
Solved! Go to Solution.
@deefunk1981 , One way is to search position of BT and then take 4 character
mid([column], search("BT",[Column],,len([Column])), 4)
Steps to follow:
1- Create a new column (by right clicking on a table and selecting new column option) and use the calculation given by @amitchandak
new_Column = mid([column1], search(" BT",[Column1],,len([Column1])), 4)
this will give you a new column with the first 4 digits of your postcode.
search(" BT"...) is going to search in your text if " BT" is there. If it is there then it will take first 4 characters(for which we have used ",4" in the formula) from BT to the right including BT.
Thank you so much for all your help everyone. The solution is now working 😁
Do all the postal codes include "BT"?
Proud to be a Super User!
Paul on Linkedin.
Yes, all the postcodes are Northern Ireland and thankfully all start with BT which makes things a lot easier.
Steps to follow:
1- Create a new column (by right clicking on a table and selecting new column option) and use the calculation given by @amitchandak
new_Column = mid([column1], search(" BT",[Column1],,len([Column1])), 4)
this will give you a new column with the first 4 digits of your postcode.
search(" BT"...) is going to search in your text if " BT" is there. If it is there then it will take first 4 characters(for which we have used ",4" in the formula) from BT to the right including BT.
Try using this M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc29DoIwFIbhW/nC3IE/RUeMuqiEAMYBGRp6Io21NccS4t1rdNTxHZ68bRtsjfSIIxyI+0Fa/0DvRvYCGzVJVqg9E72zoImfAqsmmWGeFwJHqz0p7LS9KHcLOtEGSYh6IDLaopR8FdifxzCMszXx16YLLPPyr40yVG6SxjiLSgmcJDPZu9PWf64pkqb6ld0L", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [project.address_full = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"project.address_full", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "project.address_full", "project.address_full - Copy"),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Duplicated Column", "Text Before Delimiter", each Text.BeforeDelimiter([#"project.address_full - Copy"], ", United Kingdom"), type text),
#"Inserted Last Characters" = Table.AddColumn(#"Inserted Text Before Delimiter", "Last Characters", each Text.End([Text Before Delimiter], 8), type text),
#"Inserted First Characters" = Table.AddColumn(#"Inserted Last Characters", "First Characters", each Text.Start([Last Characters], 4), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted First Characters",{"project.address_full - Copy", "Text Before Delimiter", "Last Characters"})
in
#"Removed Columns"
Proud to be a Super User!
Paul on Linkedin.
@deefunk1981 , One way is to search position of BT and then take 4 character
mid([column], search("BT",[Column],,len([Column])), 4)
Thanks for your reply. This solution makes sense and I think it will work. However, I am unsure what process to follow to use this.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
136 | |
103 | |
101 | |
74 | |
59 |
User | Count |
---|---|
261 | |
123 | |
114 | |
97 | |
84 |