Forum Discussion
Remove rows of certain columns with Query
3 steps:
- Convert table to list of columns
- Transform all entries in the list (remove first nulls and blanks)
- Convert list of columns back to table:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lFSitWJVgLSxgZglhFIzMQAJmoKZMUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ColumnA = _t, ColumnB = _t]),
TableToColumns = Table.ToColumns(Source),
DeleteNulls = List.Transform(TableToColumns, each if List.First(_)=null or List.First(_) = "" then List.Skip(_,1) else _),
Reassemble = Table.FromColumns(DeleteNulls, Table.ColumnNames(Source))
in
ReassembleImke Feldmann
www.TheBIccountant.com -- How to integrate M-code into your solution -- Check out more PBI- learning resources here
Advocate IHello,
Can you please specify the problem is more detail - may be using an image of a sample table.
(Using excel terminology here) Would you like to remove the value of say cell B2, D2 and F2 and replace them by zero or shift the cells up? (assuming row 1 is all column headers)
Hello bizbi,
I have the following table:
COLUMN A COLUMN B COLUMN C COLUMN D COLUMN E 100 60 60 40 30 … … … … … … … … … … … … … … … … … … … … … … … … … … … … … … What I want is to eliminate the cells B2 and E2, moving the whole column B and E one row up, so that the table is as follows:
COLUMN A COLUMN B COLUMN C COLUMN D COLUMN E 100 40 60 60 30 … … … … … … … … … … … … … … … … … … … … … … … … … … … … … … Thank you very much and best regards.
- ImkeF
Community Champion
3 steps:
- Convert table to list of columns
- Transform all entries in the list (remove first nulls and blanks)
- Convert list of columns back to table:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lFSitWJVgLSxgZglhFIzMQAJmoKZMUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ColumnA = _t, ColumnB = _t]),
TableToColumns = Table.ToColumns(Source),
DeleteNulls = List.Transform(TableToColumns, each if List.First(_)=null or List.First(_) = "" then List.Skip(_,1) else _),
Reassemble = Table.FromColumns(DeleteNulls, Table.ColumnNames(Source))
in
ReassembleImke Feldmann
www.TheBIccountant.com -- How to integrate M-code into your solution -- Check out more PBI- learning resources here
jthomsonSolution Sage
If you weren't wanting to retain the blank cell in columns A/C/D you could have transposed the table, merged the first two columns and then transposed it back again