Merging columns with one master overwriting blank cells

[syedn07]

I have a merged table from power query (full outer). now I have columns AA B C DD EE and lots of null fields.

Now how do I merge the columns but only overwrite blanks from one column and discard if not required.

I need to merge columns 1 and 2. I want to merge the columns so that column 2 is the "master" column and column 1 only fills in the blank cells of column 2.

As the source is dynamic the results also should be dynamic and updated when refreshed.

A1	A2	B1	C1	D1	D2	E1	E2
001	001	bb1	cc1	dd1	dd2	ee1	ee2
	002				dd2		ee2
	003				dd2		ee2
004		bb1	cc1	dd1		ee1
005		bb1	cc1	dd1		ee1
006	006	bb1	cc1	dd1	dd2	ee1	ee2
007	007	bb1	cc1	dd1	dd2	ee1	ee2
008		bb1	cc1	dd2		ee1
009	009	bb1	cc1	dd1	dd2	ee1	ee2
	010				dd2		ee2

This is how the table looks now got the columns from both lists.

Now I want to merge 1&2s if there two data points, 1 is the priority (A1 overwrites A2, D1 Overwrites D2 etc..)

I have a grasp of excel but am by no means an expert, however, I can learn and figure it out if given the steps.

 

[JGordon11]

These two sentences seem contradictory:

I need to merge columns 1 and 2. I want to merge the columns so that column 2 is the "master" column and column 1 only fills in the blank cells of column 2.

Now I want to merge 1&2s if there two data points, 1 is the priority (A1 overwrites A2, D1 Overwrites D2 etc..)

 

[syedn07]

These two sentences seem contradictory:

sorry yes, should be that column 1 is the "master" column and column 2 only fills in the blank cells of column 1.

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    xlst2 = List.RemoveNulls(List.Transform(Table.ColumnNames(Source), each if Text.End(_,1)="1" then null else _)),
    xlst1 = List.Transform(xlst2, each Text.Start(_, Text.Length(_)-1) & "1"),
    xlst = List.Zip({xlst1,xlst2}),
    Cols = List.Transform(xlst, each List.Zip({Table.Column(Source, _{0}), Table.Column(Source,_{1})})),
    Cols1 = List.Transform(Cols, each List.Transform(_, (x)=> if x{0}<> null then x{0} else x{1})),
    tblNoChange = Table.SelectColumns(Source,List.RemoveItems(Table.ColumnNames(Source), xlst1 & xlst2)),
    ColOrder = Table.ColumnNames(Table.SelectColumns(Source,List.RemoveItems(Table.ColumnNames(Source), xlst2))),
    tbl = Table.FromColumns(Table.ToColumns(tblNoChange) & Cols1, Table.ColumnNames(tblNoChange) & xlst1),
    Result = Table.ReorderColumns(tbl, ColOrder)
in
    Result

PQ Column Merge if null.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	A1	A2	B1	C1	D1	D2	E1	E2		A1	B1	C1	D1	E1
2	1	1	bb1	cc1	dd1	dd2	ee1	ee2		1	bb1	cc1	dd1	ee1
3		2				dd2		ee2		2			dd2	ee2
4		3				dd2		ee2		3			dd2	ee2
5	4		bb1	cc1	dd1		ee1			4	bb1	cc1	dd1	ee1
6	5		bb1	cc1	dd1		ee1			5	bb1	cc1	dd1	ee1
7	6	6	bb1	cc1	dd1	dd2	ee1	ee2		6	bb1	cc1	dd1	ee1
8	7	7	bb1	cc1	dd1	dd2	ee1	ee2		7	bb1	cc1	dd1	ee1
9	8		bb1	cc1	dd2		ee1			8	bb1	cc1	dd2	ee1
10	9	9	bb1	cc1	dd1	dd2	ee1	ee2		9	bb1	cc1	dd1	ee1
11		10				dd2		ee2		10			dd2	ee2
12
Sheet2