Forum Discussion
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:
| 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
Hi Anonymous
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.
2 Replies
Anonymous 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.
Hi Anonymous
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.
