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.

Answer 1

I think the syntax you want is like this:

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