Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Earth-2000GMT
Helper I
Helper I

extracting text from column using DAX

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:

PropertyIDaddressStateStatus
102(1 Mar Street - Sold)1021 Mar Street Sold
102 (1 Mar Street - Sold)1021 Mar Street Sold
103 (12 Waler Street - North Sydney - Sold)10312 Waler StreetNorth SydneySold
107 (40 Gorge Street - Sydney - Sold)10740 Gorge StreetSydneySold
109 (5 Cols Street - Sydney)1095 Cols StreetSydney 
     
     
     
     

 

 

Thank you

1 ACCEPTED SOLUTION
danextian
Super User
Super User

Hi @Earth-2000GMT 

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"

danextian_0-1742268542810.png

The applied transformations are not entirely accurate due to inconsistencies in the address data.

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

2 REPLIES 2
danextian
Super User
Super User

Hi @Earth-2000GMT 

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"

danextian_0-1742268542810.png

The applied transformations are not entirely accurate due to inconsistencies in the address data.

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
parry2k
Super User
Super User

@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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.