Power Query List.RemoveNulls on Column Collection

Question

I have a table as such in an Excel workbook. This table is brought into Power Query (link to image: Table shown in Query Window).

tblTest:
+------+------+------+------+
| Col1 | Col2 | Col3 | Col4 |
+------+------+------+------+
| 1    | 2    | null | null |
| null | null | 1    | null |
| null | 3    | null | null |
| null | null | null | 4    |
| 2    | 5    | null | null |
+------+------+------+------+

In Power Query I make a new table by removing nulls in each column as such:

let
    Source = Excel.CurrentWorkbook(){[Name="tblTest"]}[Content],    
    NewTable = Table.FromColumns({
    List.RemoveNulls(Source[Col1]),
    List.RemoveNulls(Source[Col2]),
    List.RemoveNulls(Source[Col3]),
    List.RemoveNulls(Source[Col4])
    },Table.ColumnNames(Source))
in
    NewTable

My output from Power Query (NewTable):

NewTable:
+------+------+------+------+
| Col1 | Col2 | Col3 | Col4 |
+------+------+------+------+
| 1    |    2 | 1    | 4    |
| 2    |    3 | null | null |
| null |    5 | null | null |
+------+------+------+------+

NewTable is my expected output.

My problem is that there are an unknown amount of columns in the input table (tblTest). I need some M language code that will loop over all of the columns in the input table, regardless of how many, and remove nulls in each column, and ultimately result in NewTable.

From what I understand, actual looping (like for loop) isn't something that is really done too much in M, so I would guess that there's some kind of collection method that would do this all in one go. I just for the life of me can't figure out what that may be.

I would expect the code to look something like this:

    let
    Source = Excel.CurrentWorkbook(){[Name="tblTest"]}[Content],
    lstColumns = Table.ColumnNames(Source),    
    NewTable = Table.FromColumns(each List.RemoveNulls(each lstColumns),lstColumns)
in
    NewTable

The code above does not work and results in the following error:

Expression.Error: We cannot convert a value of type Function to type List.

Any suggestions would be greatly appreciated.

Your first mistake is that there are no Null values on an Excel worksheet. — ashleedawg
Hi ashleedawg. I literally just copied and pasted what was shown in the Query editor. — Aye_Aye_Frey
What is your actual m code? I am unclear on how you are removing nulls? Replacing I could understand but removing might affect the table structure in terms of number of rows. Are you expecting a new output table that a fixed number of rows across all columns i.e. a table where new nulls appear to fill out the missing row values in some columns? — QHarr
QHarr: The M code that creates NewTable is shown in the 2nd code paragraph, where I hard-code the 4 columns.I am expecting NewTable as output. — Aye_Aye_Frey
QHarr: I am removing nulls individually in each column (as a column list). These new lists (one for each column) populate NewTable. — Aye_Aye_Frey

Alexis Olson Alexis Olson · Accepted Answer · 2018-03-19T14:56:04

I think the syntax you want is like this:

ToCols = Table.ToColumns(Source),
NewTable = Table.FromColumns(List.Transform(ToCols, each List.RemoveNulls(_)))

Power Query List.RemoveNulls on Column Collection

1 Answers