Transform all rows in a column to delimited list, then delete text between delimiters

[laganlee]

Hi everybody.

I have a table with one colum and many rows that I need to transform to a line of text.

I need to do this as I want to remove unwanted text between the words  "First"  and "Total"

For example :

Column Name

test text  xxxxxxxxxxx

First xremove this text in red xxxxxxx 

xxxxxxxxxxxxxxxxxx

xxxxxxxxxxxxxxxxx

Total text text text  

I want this text xxxxxxx

test text  xxxxxxxxxxx

First x remove more text this text in red xxxxxxx 

xxxxxxxxxxxxxxxxxx

xxxxxxxxxxxxxxxxx

Total Copied 

I want also keep this text xxxxxxx

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

I have tried transpose but there are too many rows (thousands) for Power BI to cope with.

[laganlee]

Hi Pat

That worked a treat!

Really appreciate that; what a fantastic solution 🙂 

[mahoneypat · Accepted solution]

Here is one way to do it with a custom column, fill down, and some filters. Just create a blank query, open the Advanced Editor, and replace the code there with the below, to see how it works. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rY69CoAwDIRf5ejsWwiCu1txKBiw+BNpg/bxFau0oODiDRkul8untRLyAqEgQEhSbaFVZd2xC44mXgnSWx+Ddoaj7o7jzIaH3u3TbVjMGLvSiD01NjNL9iy/+2bFBTuxo6v7b+ySF3vU5LRm9IyBaHnjbnc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TextColumn = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TextColumn", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([TextColumn], "First") then "First" else if Text.Contains([TextColumn], "Total") then "Total" else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Custom] <> "First")),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Custom.1", each if Text.Contains([TextColumn], "Total") then "N" else "Y"),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = "Y")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Custom", "Custom.1"})
in
    #"Removed Columns"

Pat