Forum Discussion

Frequent Visitor

7 years ago

Solved

Extract text Between Delimiters with varying text

Hello,

I have data imported from excel and am trying to split data using Power Query Editor - Extract text Between Delimiters. But I am facing challenges with the Extract text Between Delimiters feature due to the data I have.

The data imported looks like

Table1.ExpenseSummary

---------------------------

Hotel $112.23;Food $45.45; Taxi on 03/23/19 $13.90; Wifi $12.12;

Taxi on 03/25/19 $19.30; Mobile 12.34;Hotel on 3 April 19 - $100.10;Food $30.45;

....

The sequence of Expense Heads is not the same in every row. The expense value will always start with a $ sign

The range of expense heads is limited, so 1 row could have Food, Taxi and not have Hotel, Wifi

the next row may have all 4.

How can i have the data split into columns

Expense Summary | Hotel | Food | Taxi | Wifi

Hotel $112.23;Food $4.... 112.23 45.45 13.90 12.12

Taxi on 03/25/19 19.30..... 100.10 30.45 19.30 0

Any help will be greatly appreciated. Thank you.

Need Help

v-juanli-msft

7 years ago

Hi shujak

Please download my pbix to see more details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc29CsIwFEDhV7mEjnp7f5IhZHIRFzfBIWRQrBAIpohDH79X6uB44IOTszv1z9RgYBYUTcfeHzD4gD4kuNyWCv0FpKPoyNGUYqQE1/qsFoIsyZVddv8ybDKimjz3e20TGFWftpUxhcP8rg0M7o0SIdNvrfRdu1JW", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "Expense Summary"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Index", "Expense Summary", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Expense Summary.1", "Expense Summary.2", "Expense Summary.3", "Expense Summary.4", "Expense Summary.5"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Expense Summary.1", type text}, {"Expense Summary.2", type text}, {"Expense Summary.3", type text}, {"Expense Summary.4", type text}, {"Expense Summary.5", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Expense Summary.5"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Index"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Columns", "condition", each if Text.Contains([Value], "$") then 1 else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each [condition] <> null and [condition] <> ""),
    #"Trimmed Text" = Table.TransformColumns(#"Filtered Rows",{{"Value", Text.Trim, type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Value", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3", "Value.4", "Value.5", "Value.6", "Value.7"}),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Split Column by Delimiter1", {"Index", "Attribute", "Value.1", "condition"}, "Attribute.1", "Value"),
    #"Added Conditional Column1" = Table.AddColumn(#"Unpivoted Columns1", "condition2", each if Text.Contains([Value], "$") then 1 else null),
    #"Filtered Rows1" = Table.SelectRows(#"Added Conditional Column1", each [condition2] <> null and [condition2] <> ""),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Attribute", "condition", "Attribute.1", "condition2"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Value.1]), "Value.1", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"Index", "Hotel", "Food", "Taxi", "Wifi"})
in
    #"Replaced Value"

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Extract text Between Delimiters with varying text.pbix32 KB

5 Replies

v-juanli-msft

Community Support

7 years ago

Hi shujak

Please download my pbix to see more details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc29CsIwFEDhV7mEjnp7f5IhZHIRFzfBIWRQrBAIpohDH79X6uB44IOTszv1z9RgYBYUTcfeHzD4gD4kuNyWCv0FpKPoyNGUYqQE1/qsFoIsyZVddv8ybDKimjz3e20TGFWftpUxhcP8rg0M7o0SIdNvrfRdu1JW", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "Expense Summary"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Index", "Expense Summary", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Expense Summary.1", "Expense Summary.2", "Expense Summary.3", "Expense Summary.4", "Expense Summary.5"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Expense Summary.1", type text}, {"Expense Summary.2", type text}, {"Expense Summary.3", type text}, {"Expense Summary.4", type text}, {"Expense Summary.5", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Expense Summary.5"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Index"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Columns", "condition", each if Text.Contains([Value], "$") then 1 else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each [condition] <> null and [condition] <> ""),
    #"Trimmed Text" = Table.TransformColumns(#"Filtered Rows",{{"Value", Text.Trim, type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Value", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3", "Value.4", "Value.5", "Value.6", "Value.7"}),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Split Column by Delimiter1", {"Index", "Attribute", "Value.1", "condition"}, "Attribute.1", "Value"),
    #"Added Conditional Column1" = Table.AddColumn(#"Unpivoted Columns1", "condition2", each if Text.Contains([Value], "$") then 1 else null),
    #"Filtered Rows1" = Table.SelectRows(#"Added Conditional Column1", each [condition2] <> null and [condition2] <> ""),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Attribute", "condition", "Attribute.1", "condition2"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Value.1]), "Value.1", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"Index", "Hotel", "Food", "Taxi", "Wifi"})
in
    #"Replaced Value"

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Extract text Between Delimiters with varying text.pbix32 KB

shujak
Frequent Visitor
7 years ago
Thanks a lot Maggie. This solution works for me. Thanks again.

Zubair_Muhammad

Community Champion

7 years ago

shujak

here is another way

Please see attached file as well for steps

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc0xCgIxEEDRqwxhS52dzCRFmMpGbOwEi5BCcYVAMItYeBzP4skcWQvLDw9+zm7XH1ODwXtGFt32foEhRAxR4XB6Vug3IBlZRp9MCSZSONZrtWD0rK6ssvuXcZEJxeS+n2ubwKgEXVbGBDbzvTYwuDZKhJ5+a6Hv+v1ypXwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Expense Summary" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Expense Summary", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.SplitAny([Expense Summary],";")),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Custom", "Custom", Splitter.SplitTextByDelimiter("$", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type number}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Custom.1", Text.Trim, type text}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Trimmed Text", {{"Custom.1", each Text.BeforeDelimiter(_, " "), type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Extracted Text Before Delimiter", each ([Custom.2] <> null)),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Custom.1]), "Custom.1", "Custom.2", List.Sum)
in
    #"Pivoted Column"

Extract text Between Delimiters with varying text.pbix31 KB

shujak
Frequent Visitor
7 years ago
Hi Zubair_Muhammad ,

Thanks a lot for the solution. If i may ask a question, could you pls tell me is there a way to use a wild-card Like or % to do a text search (instead of = Table.TransformColumns(#"Trimmed Text", {{"Custom.1", each Text.BeforeDelimiter(_, " "), type text}})) in case the ExpenseHead is buried in between text. for e.g.
03/25/19 - Taxi - $19.30; Mobile 12.34;3 April 19 - Hotel - $100.10;Food $30.45;

Thanks in advance.
- Zubair_Muhammad
  Community Champion
  7 years ago
  shujak
  
  I believe wild cards are not an option but there is a workaround. Will get back to you later in the day.

Forum Discussion

Extract text Between Delimiters with varying text

5 Replies

Related content

Extracting text before a delimiter

extract text before delimiter

DAX: extracting string using delimiter

DAX extract text between delimiter

Extract delimited values to new table