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
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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi Pat
That worked a treat!
Really appreciate that; what a fantastic 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
62 | |
21 | |
18 | |
12 |