Reply
laganlee
Helper II
Helper II
Partially syndicated - Outbound

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

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.

 

 

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

Syndicated - Outbound

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
laganlee
Helper II
Helper II

Syndicated - Outbound

Hi Pat

That worked a treat!

Really appreciate that; what a fantastic solution 🙂 

mahoneypat
Microsoft Employee
Microsoft Employee

Syndicated - Outbound

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)