Forum Discussion
Non-DAX solution please - transform text field to remove coding
I need to create a report from my SharePoint list which includes content entered as free text. In Power BI, the field presents thus:
<div class="ExternalClass88AE48E489694305A0786C84C2AD6A34"><div style="font-family:Calibri, Arial, Helvetica, sans-serif;font-size:11pt;color:rgb(0, 0, 0);"><span style="color:black;">Brown cows round mounds near towns.<div style="margin-top:14.6667px;margin-bottom:14.6667px;">Mound-rounding cows are often brown.<br></div></span></div></div>
How do I transform this, with a non-DAX solution, so that only the following text is pulled into my report? Bold format is for emphasis only.
Brown cows round mounds near towns.
Mound-rounding cows are often brown.
Not entirely clear what your expected output format is but here is a non-DAX solution:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY9NTsMwEIWvMgobkJKSNqnrKmIRQiU2nKDpwnadyMKxI9v9Y8WeW3IS4rYkLSCkkaXxm5n3veUyKDdxnLC12AKTxNqHMljsHTeKyML3GOeLFHc1R/M0iad5PMOowGkxyZ9QnqTl6QAfzlh3kLw7U2nlooo0Qh68OEE3U5wVRApqRAi5EUSG8MzlljvBSAiWKBtZbkSVHVeteOPD4njcuoxpqc3wZ2p6G4fg6y67BrEtUT3JjzUqCXvt5z/fPx6N3ilgemfB6I1aQ+NfC4oTA67T7Oh3vIaYWqjI6fYCMh0hhGbtPjurVDunm78Gvu1fvFV0tBWqPkEQw0FXjiugnuxsTs1lwPuO5ar3if8d6PtgtfoC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter(">", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"), #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1", Splitter.SplitTextByEachDelimiter({"<"}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1", type text}, {"Column1.2", type text}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Column1.1"}), #"Removed Blank Rows" = Table.SelectRows(#"Removed Other Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))), Result = Text.Combine(#"Removed Blank Rows"[Column1.1],"#(lf)") in ResultHow to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
1 Reply
Not entirely clear what your expected output format is but here is a non-DAX solution:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY9NTsMwEIWvMgobkJKSNqnrKmIRQiU2nKDpwnadyMKxI9v9Y8WeW3IS4rYkLSCkkaXxm5n3veUyKDdxnLC12AKTxNqHMljsHTeKyML3GOeLFHc1R/M0iad5PMOowGkxyZ9QnqTl6QAfzlh3kLw7U2nlooo0Qh68OEE3U5wVRApqRAi5EUSG8MzlljvBSAiWKBtZbkSVHVeteOPD4njcuoxpqc3wZ2p6G4fg6y67BrEtUT3JjzUqCXvt5z/fPx6N3ilgemfB6I1aQ+NfC4oTA67T7Oh3vIaYWqjI6fYCMh0hhGbtPjurVDunm78Gvu1fvFV0tBWqPkEQw0FXjiugnuxsTs1lwPuO5ar3if8d6PtgtfoC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter(">", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"), #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1", Splitter.SplitTextByEachDelimiter({"<"}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1", type text}, {"Column1.2", type text}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Column1.1"}), #"Removed Blank Rows" = Table.SelectRows(#"Removed Other Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))), Result = Text.Combine(#"Removed Blank Rows"[Column1.1],"#(lf)") in ResultHow to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".