Remove column nulls in Power Query without removing any actual values in any column

Question

I want to take the table below that contains intermittent nulls and essentially collapse the table to be just a clean list of values in each column. How can I do this in Power Query editor or possibly in Excel. I don't care that the rows get rearranged.

left	center	right
1	2	null
null	3	3
2	null	3
3	2	null

left	center	right
1	2	3
2	3	3
3	2

Answer 1

let
    Source = "Your source",
    Custom1 = Table.ToColumns( Source),
    Custom2 = List.Transform (Custom1, each List.RemoveItems(_,{null})),
    Custom3 = Table.FromColumns( Custom2, Table.ColumnNames(Source))
in
    Custom3

Answer 2

You can achieve the output in the transformation layer. Go to the Advance Query Editor of your table and add these new steps as below-

• replace the your_previous_step_name accordingly in the below script.

let
    //your existing steps in the table
    
    // new steps starts here
    //-------------------------------
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"your_previous_step_name", {}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Attribute"}, {{"Count", each _, type table [Attribute=text, Value=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"I2",1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Attribute", "Value", "I2"}, {"Custom.Attribute", "Custom.Value", "Custom.I2"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Custom.Attribute]), "Custom.Attribute", "Custom.Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Custom.I2"})
in
    #"Removed Columns"

Output-