Forum Discussion
Remove rows of certain columns with Query
Hi,
I have a table with several columns and what I want is to eliminate the first row of certain columns, NOT all of them.
I tried it in Query but it does in all the columns.
Is it possible to do what I want?
Thank you very much and best regards.
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
4 Replies
Hello,
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.
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
