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 t...
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
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.
jthomson
Solution Sage
Solution SageIf 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