Power Query: Remove null value in column, but keep other column

[mduntley]

Trying to find a way to remove all of the null column in each of the column, but not delete the other value in column 2

= #table({"Column 1", "Column 2"}, {{"439","772"},{"3241",null},{"1552","1143"},{"1308",null},{"3562","3242"},{null,"2494"},{"2043","1527"},{"1749","2162"},{"3472","1646"}})

Starting Table:

Book3
	A	B
1	Column 1	Column 2
2	439	772
3	3241
4	1552	1143
5	1308
6	3562	3242
7		2494
8	2043	1527
9	1749	2162
10	3472	1646
Sheet2

Table I want to get

Book3
	A	B
1	Column 1	Column 2
2	439	772
3	3241	1143
4	1552	3242
5	1308	2494
6	3562	1527
7	2043	2162
8	1749	1646
9	3472
Sheet3

 

[jdellasala]

I will admit this is kind of lame, but it works. First Query: Column1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Column 1", type text}, {"Column 2", type text}}),
    Column1 = Table.FromList( ChangedType[Column 1] ),
    FilteredColumn1 = Table.SelectRows(Column1, each ([Column1] <> "")),
    AddedColumn1Index = Table.AddIndexColumn(FilteredColumn1, "Index", 1, 1, Int64.Type)
in
    AddedColumn1Index

Second Query: Column2:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Column 1", type text}, {"Column 2", type text}}),
    Column2 = Table.FromList( ChangedType[Column 2] ),
    RenamedColumn2 = Table.RenameColumns(Column2,{{"Column1", "Column2"}}),
    FilteredColumn2 = Table.SelectRows(RenamedColumn2, each ([Column2] <> "")),
    AddedColumn2Index = Table.AddIndexColumn(FilteredColumn2, "Index", 1, 1, Int64.Type)
in
    AddedColumn2Index

Merge the two queries - here done as a New Query: Merge1:

let
    Source = Table.NestedJoin(Column1, {"Index"}, Column2, {"Index"}, "Column2", JoinKind.LeftOuter),
    ExpandedColumn2 = Table.ExpandTableColumn(Source, "Column2", {"Column2"}, {"Column2"}),
    RemovedOtherColumns = Table.SelectColumns(ExpandedColumn2,{"Column1", "Column2"}),
    ChangedType = Table.TransformColumnTypes(RemovedOtherColumns,{{"Column1", Int64.Type}, {"Column2", Int64.Type}})
in
    ChangedType

I manually changed the last {"Column2"} in the ExpandedColumn2 line from Column2.1.
Merge1 Table:

Book2
	D	E
1	Column1	Column2
2	439	772
3	3241	1143
4	1552	3242
5	1308	2494
6	3562	1527
7	2043	2162
8	1749	1646
9	3472
Sheet1

I have no doubt there are better solutions!

 

[mduntley]

I was trying to do it all in one Query.

 

[JGordon11]

let
    Source = #table({"Column 1", "Column 2"}, {{"439","772"},{"3241",null},{"1552","1143"},{"1308",null},{"3562","3242"},{null,"2494"},{"2043","1527"},{"1749","2162"},{"3472","1646"}}),
    lst = Table.ToColumns(Source),
    lst1 = List.Transform(lst, each List.RemoveNulls(_)),
    max = List.Accumulate(lst1, 0, (s,c)=> let lc = List.Count(c) in if lc>s then lc else s),
    lst2 = List.Transform(lst1, each _ & List.Repeat({null}, max - List.Count(_))),
    Result = Table.FromColumns(lst2, Table.ColumnNames(Source))
in
    Result

 

[jdellasala]

let
    Source = #table({"Column 1", "Column 2"}, {{"439","772"},{"3241",null},{"1552","1143"},{"1308",null},{"3562","3242"},{null,"2494"},{"2043","1527"},{"1749","2162"},{"3472","1646"}}),
    lst = Table.ToColumns(Source),
    lst1 = List.Transform(lst, each List.RemoveNulls(_)),
    max = List.Accumulate(lst1, 0, (s,c)=> let lc = List.Count(c) in if lc>s then lc else s),
    lst2 = List.Transform(lst1, each _ & List.Repeat({null}, max - List.Count(_))),
    Result = Table.FromColumns(lst2, Table.ColumnNames(Source))
in
    Result

SWEET!