Extract text Between Delimiters with varying text

[shujak]

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.

[Zubair_Muhammad]

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

[shujak]

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]

@shujak

I believe wild cards are not an option but there is a workaround. Will get back to you later in the day.

[v-juanli-msft · Accepted solution]

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.

[shujak]

Thanks a lot Maggie. This solution works for me. Thanks again.