Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
laganlee
Helper II
Helper II

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

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

Hi Pat

That worked a treat!

Really appreciate that; what a fantastic solution 🙂 

mahoneypat
Microsoft Employee
Microsoft Employee

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


Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors