Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi All,
I have this column:
Property |
102(1 Mar Street - Sold) |
102 (1 Mar Street - Sold) |
103 (12 Waler Street - North Sydney - Sold) |
103 (12 Waler Street - North Sydney - Sold) |
101 (4 & 5 Found Rd Laverton) |
104 (38 Dertys Road - Laverton) |
105(6 Tilda Road - Sydney) |
107 (40 Gorge Street - Sydney - Sold) |
108 (30 Kent Street) |
101 (4 & 5 Found Rd Laverton) |
109 (5 Cols Street - Sydney) |
what I would like to have is:
Property | ID | address | State | Status |
102(1 Mar Street - Sold) | 102 | 1 Mar Street | Sold | |
102 (1 Mar Street - Sold) | 102 | 1 Mar Street | Sold | |
103 (12 Waler Street - North Sydney - Sold) | 103 | 12 Waler Street | North Sydney | Sold |
107 (40 Gorge Street - Sydney - Sold) | 107 | 40 Gorge Street | Sydney | Sold |
109 (5 Cols Street - Sydney) | 109 | 5 Cols Street | Sydney | |
Thank you
Solved! Go to Solution.
This is better off done with M than in DAX. This will be a very long and possibly nasty code with DAX. Below is a sample M based on your data.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pY/LCsIwEEV/5dJVBIX0aV0ruvCxaAUXtYtABhVCAmkq9O8NaqEUEcTtnMOcmaoKQh6xEHthUTpL5DBDaZScBPX0CfGNxp5GOAlFA+FgrLui7KSm7k8/BEtwbjmPMqRYm1ZLFBI7cSfrjO61BCzOsfKzrkFhhPRrxk7KMhxvSopeeAV7PPcljo2xFxq8+umm3Mc4tqTdW/zx2AVYiqVRzbjjhfoB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Property = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Property", type text}}),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type", "ID", each Text.BeforeDelimiter([Property], "("), type text),
#"Inserted Text Between Delimiters" = Table.AddColumn(#"Inserted Text Before Delimiter", "Address", each Text.BeforeDelimiter(Text.BetweenDelimiters([Property], "(", "-"), ")"), type text),
#"Inserted Text Between Delimiters1" = Table.AddColumn(#"Inserted Text Between Delimiters", "State", each Text.Trim(Text.BeforeDelimiter(Text.BetweenDelimiters([Property], "-", "-"), ")")), type text),
#"Inserted Text Between Delimiters2" = Table.AddColumn(#"Inserted Text Between Delimiters1", "Status", each
if Text.Contains([Property], "-") then
Text.BetweenDelimiters([Property], "-", ")", {0, RelativePosition.FromEnd}, 0) else null, type text)
in
#"Inserted Text Between Delimiters2"
The applied transformations are not entirely accurate due to inconsistencies in the address data.
This is better off done with M than in DAX. This will be a very long and possibly nasty code with DAX. Below is a sample M based on your data.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pY/LCsIwEEV/5dJVBIX0aV0ruvCxaAUXtYtABhVCAmkq9O8NaqEUEcTtnMOcmaoKQh6xEHthUTpL5DBDaZScBPX0CfGNxp5GOAlFA+FgrLui7KSm7k8/BEtwbjmPMqRYm1ZLFBI7cSfrjO61BCzOsfKzrkFhhPRrxk7KMhxvSopeeAV7PPcljo2xFxq8+umm3Mc4tqTdW/zx2AVYiqVRzbjjhfoB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Property = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Property", type text}}),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type", "ID", each Text.BeforeDelimiter([Property], "("), type text),
#"Inserted Text Between Delimiters" = Table.AddColumn(#"Inserted Text Before Delimiter", "Address", each Text.BeforeDelimiter(Text.BetweenDelimiters([Property], "(", "-"), ")"), type text),
#"Inserted Text Between Delimiters1" = Table.AddColumn(#"Inserted Text Between Delimiters", "State", each Text.Trim(Text.BeforeDelimiter(Text.BetweenDelimiters([Property], "-", "-"), ")")), type text),
#"Inserted Text Between Delimiters2" = Table.AddColumn(#"Inserted Text Between Delimiters1", "Status", each
if Text.Contains([Property], "-") then
Text.BetweenDelimiters([Property], "-", ")", {0, RelativePosition.FromEnd}, 0) else null, type text)
in
#"Inserted Text Between Delimiters2"
The applied transformations are not entirely accurate due to inconsistencies in the address data.
@Earth-2000GMT why DAX? DAX is not good for these kind of transformation/data preparations, why not Power Query? Use the right technology/tool that it is meant for.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
66 | |
34 | |
26 | |
22 |
User | Count |
---|---|
97 | |
96 | |
58 | |
45 | |
42 |