Forum Discussion
Remove Blank Line within the Column Cell
I have the following code in my Power BI.
After removing some names (line Cleaned Users), empty spaces are left behind. How can I remove these spaces and shift the next line up without leaving any gaps?
Additionally, there is an empty space after the last words; how can I remove that as well?
let
Source = Pdf.Tables(Web.Contents("https://sharepoint.com/sites/Infolinks/Key%20Data.pdf"), [Implementation="1.3"]),
FilteredTables = Table.SelectRows(Source, each [Kind] = "Table"),
ExpandedData = Table.ExpandTableColumn(FilteredTables, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}),
#"Removed Columns" = Table.RemoveColumns(ExpandedData, {"Id", "Name", "Kind", "Column4", "Column5", "Column6"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns", 2),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([keyTag name] = "Klift" or [keyTag name] = "Nlift" or [keyTag name] = "TClift")),
#"Replaced LF" = Table.ReplaceValue(#"Filtered Rows", "#(lf)", " ", Replacer.ReplaceText, {"Users"}),
#"Replaced Bullet" = Table.ReplaceValue(#"Replaced LF", "•", "#(cr)#(lf)", Replacer.ReplaceText, {"Users"}),
#"Cleaned Users" = List.Accumulate(
{"SU-Sin", "Admin", "SU-Be", "TS-Lim", "Co", "2-WSH Card"},
#"Replaced Bullet",
(currentTable, wordToRemove) => Table.ReplaceValue(currentTable, wordToRemove, "", Replacer.ReplaceText, {"Users"})
),
#"Trimmed Text" = Table.TransformColumns(#"Cleaned Users", {{"Users", Text.Trim, type text}}),
#"Replaced Apostrophe" = Table.ReplaceValue(#"Trimmed Text", "'", "'", Replacer.ReplaceText, {"Users"})
in
#"Replaced Apostrophe"
Hi Amos_Lim
That is possibly because of the replace value
"#(cr)#(lf)"In the naked eye, they both appear as a line separator and, if together, will appear as two lines. Try using just one of them. If there still are blank lines, try the custom column below:
let //replace with the actual delimiter delimiter = "#(lf)", split = Text.Split([Column1],delimiter ), filtered = List.Select( split, each _ <> null and _ <> ""), combined = Text.Combine (filtered, delimiter) in combined
4 Replies
Hello Amos_Lim
Try the below M Query
let
Source = Pdf.Tables(Web.Contents("https://sharepoint.com/sites/Infolinks/Key%20Data.pdf"), [Implementation="1.3"]),
FilteredTables = Table.SelectRows(Source, each [Kind] = "Table"),
ExpandedData = Table.ExpandTableColumn(FilteredTables, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}),
#"Removed Columns" = Table.RemoveColumns(ExpandedData, {"Id", "Name", "Kind", "Column4", "Column5", "Column6"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns", 2),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([keyTag name] = "Klift" or [keyTag name] = "Nlift" or [keyTag name] = "TClift")),
#"Replaced LF" = Table.ReplaceValue(#"Filtered Rows", "#(lf)", " ", Replacer.ReplaceText, {"Users"}),
#"Replaced Bullet" = Table.ReplaceValue(#"Replaced LF", "•", "#(cr)#(lf)", Replacer.ReplaceText, {"Users"}),
#"Cleaned Users" = List.Accumulate(
{"SU-Sin", "Admin", "SU-Be", "TS-Lim", "Co", "2-WSH Card"},
#"Replaced Bullet",
(currentTable, wordToRemove) => Table.ReplaceValue(currentTable, wordToRemove, "", Replacer.ReplaceText, {"Users"})
),
#"Trimmed Text" = Table.TransformColumns(#"Cleaned Users", {{"Users", Text.Trim, type text}}),
#"Removed Blank Rows" = Table.SelectRows(#"Trimmed Text", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Replaced Apostrophe" = Table.ReplaceValue(#"Removed Blank Rows", "'", "'", Replacer.ReplaceText, {"Users"})
in
#"Replaced Apostrophe"--------------------------------
Or you can just follow these steps;
go to Transform Data -> Go to the table -> select the column -> check the remove rows in the header section -> click & select remove blank/empty rows -> check the table-----------------------------------
If it still did not work then
select the column and expand and click on "Remove Empty"
Thanks & RegardsThanks, Biswajit Das.
It seems the code isn't working as expected. I think I might not have explained it clearly. The photo shows the data within each cell.
In this case, I have three rows of data, and under the "Users" column, each cell contains multiple names. My original code is designed to remove unwanted names from these cells. However, after removing the specified names, I am left with empty lines in all three rows, as each cell initially contained the names I wanted to remove.
Hi,
In the Filter drop down of that column, there is an option to Remove Empty. Have you tried that?
Hi Amos_Lim
That is possibly because of the replace value
"#(cr)#(lf)"In the naked eye, they both appear as a line separator and, if together, will appear as two lines. Try using just one of them. If there still are blank lines, try the custom column below:
let //replace with the actual delimiter delimiter = "#(lf)", split = Text.Split([Column1],delimiter ), filtered = List.Select( split, each _ <> null and _ <> ""), combined = Text.Combine (filtered, delimiter) in combined
