Extract text Between Delimiters with varying text

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.