extracting text from column using DAX

[Earth-2000GMT]

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

[danextian · Accepted solution]

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"

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

[parry2k]

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