- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Pat
That worked a treat!
Really appreciate that; what a fantastic solution 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
12-10-2023 06:07 AM | |||
07-09-2024 12:42 AM | |||
01-14-2024 09:27 PM | |||
03-14-2024 10:50 AM | |||
03-21-2024 07:38 AM |