Remove rows of certain columns with Query

[Anonymous]

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.

[bizbi]

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)

[Anonymous]

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 · Accepted solution]

3 steps:

1. Convert table to list of columns
2. Transform all entries in the list (remove first nulls and blanks)
3. 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
    Reassemble

Imke Feldmann

www.TheBIccountant.com -- How to integrate M-code into your solution  -- Check out more PBI- learning resources here

[jthomson]

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