Merging Queries including ALL Rows

[TheMacroNoob]

Hello excel experts,

I am trying to merge two profit and loss statements which contain the same list of unique properties but contain some different GLs. The two statements are an "Actual" and a "Budget", which is why there are differences at times. I would like my third query to merge the two by including ALL GLs that have a number on either table, and correctly list the property and account name, while zeroing out (or null I guess) the actual or budget column if there was no match. I understand the various join types like LeftOuter, RightOuter, FullOuter, and Inner, but I can't get the desired result. My example off to the right of the data example is using a FullOuter join type with "GL" as the selected column to match, but of course that duplicates values and omits the GL that was missing on the Actual but contained on Budget table/query. I have tried Property too, or GL + Property for matches, and different join types with no success.

See below for example data. In the first table, "Actual", there are 4 instances of account "A", while the budget contains 3 instances of account "A". The desired result will still show 4 instances of account "A", but zeroing out the budget of the property or GL that wasn't found on Budget, and vice versa. How on Earth do I achieve this?

Query Problem.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
3			Actual					Budget					Desired Result						My Result
4
5		GL	Account	Property	Actual		GL	Account	Property	Budget		GL	Account	Property	Actual	Budget		GL	Account	Property	Actual	Budget
6		0000-1000	A	107	30608		0000-1000	A	107	7399		0000-1000	A	107	30608	7399		0000-1000	A	107	30608	7399
7		0000-1000	A	109	26792		0000-1000	A	109	1641		0000-1000	A	109	26792	1641		0000-1000	A	107	30608	1641
8		0000-1000	A	110	5436		0000-1000	A	110	3598		0000-1000	A	110	5436	3598		0000-1000	A	107	30608	3598
9		0000-1000	A	320	5333		0000-1001	B	107	5466		0000-1000	A	320	5333	0		0000-1000	A	109	26792	7399
10		0000-1001	B	107	16985		0000-1001	B	110	89		0000-1001	B	107	16985	5466		0000-1000	A	109	26792	1641
11		0000-1001	B	110	20879		0000-1001	B	320	4314		0000-1001	B	110	20879	89		0000-1000	A	109	26792	3598
12		0000-1001	B	320	7837		0000-1008	C	107	912		0000-1001	B	320	7837	4314		0000-1000	A	110	5436	7399
13		0000-1008	C	107	14531		0000-1008	C	110	3085		0000-1008	C	107	14531	912		0000-1000	A	110	5436	1641
14		0000-1008	C	109	40266		0000-1008	C	320	6483		0000-1008	C	109	40266	0		0000-1000	A	110	5436	3598
15		0000-1008	C	110	50223		0000-1008	C	658	3732		0000-1008	C	110	50223	3085		0000-1000	A	320	5333	7399
16		0000-1008	C	320	4165		0000-1009	D	110	981		0000-1008	C	320	4165	6483		0000-1000	A	320	5333	1641
17		0000-1008	C	658	38958		0000-1009	D	320	1770		0000-1008	C	658	38958	3732		0000-1000	A	320	5333	3598
18												0000-1009	D	110	0	981		0000-1001	B	107	16985	5466
19												0000-1009	D	320	0	1770		0000-1001	B	107	16985	89
20																		0000-1001	B	107	16985	4314
21																		0000-1001	B	110	20879	5466
22																		0000-1001	B	110	20879	89
23																		0000-1001	B	110	20879	4314
24																		0000-1001	B	320	7837	5466
25																		0000-1001	B	320	7837	89
26																		0000-1001	B	320	7837	4314
27																		0000-1008	C	107	14531	912
28																		0000-1008	C	107	14531	3085
29																		0000-1008	C	107	14531	6483
30																		0000-1008	C	107	14531	3732
31																		0000-1008	C	109	40266	912
32																		0000-1008	C	109	40266	3085
33																		0000-1008	C	109	40266	6483
34																		0000-1008	C	109	40266	3732
35																		0000-1008	C	110	50223	912
36																		0000-1008	C	110	50223	3085
37																		0000-1008	C	110	50223	6483
38																		0000-1008	C	110	50223	3732
39																		0000-1008	C	320	4165	912
40																		0000-1008	C	320	4165	3085
41																		0000-1008	C	320	4165	6483
42																		0000-1008	C	320	4165	3732
43																		0000-1008	C	658	38958	912
44																		0000-1008	C	658	38958	3085
45																		0000-1008	C	658	38958	6483
46																		0000-1008	C	658	38958	3732
47																						981
48																						1770
49
50
Help

 

[alansidman]

let
    Source = Table.NestedJoin(Actual, {"GL", "Account", "Property"}, Budget, {"GL", "Account", "Property"}, "Budget", JoinKind.FullOuter),
    #"Expanded Budget" = Table.ExpandTableColumn(Source, "Budget", {"GL", "Account", "Property", "Budget"}, {"Budget.GL", "Budget.Account", "Budget.Property", "Budget.Budget"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Budget", "Custom", each if [GL]= null then [Budget.GL] else [GL]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Account]=null then [Budget.Account] else [Account]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [Property]= null then [Budget.Property] else [Property]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"GL", "Account", "Property", "Budget.GL", "Budget.Account", "Budget.Property"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Custom.1", "Custom.2", "Actual", "Budget.Budget"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "GL"}, {"Custom.1", "Account"}, {"Custom.2", "Propetty"}, {"Budget.Budget", "Budget"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Column1", "Column2"})
in
    #"Removed Columns1"

 

[TheMacroNoob]

let
    Source = Table.NestedJoin(Actual, {"GL", "Account", "Property"}, Budget, {"GL", "Account", "Property"}, "Budget", JoinKind.FullOuter),
    #"Expanded Budget" = Table.ExpandTableColumn(Source, "Budget", {"GL", "Account", "Property", "Budget"}, {"Budget.GL", "Budget.Account", "Budget.Property", "Budget.Budget"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Budget", "Custom", each if [GL]= null then [Budget.GL] else [GL]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Account]=null then [Budget.Account] else [Account]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [Property]= null then [Budget.Property] else [Property]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"GL", "Account", "Property", "Budget.GL", "Budget.Account", "Budget.Property"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Custom.1", "Custom.2", "Actual", "Budget.Budget"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "GL"}, {"Custom.1", "Account"}, {"Custom.2", "Propetty"}, {"Budget.Budget", "Budget"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Column1", "Column2"})
in
    #"Removed Columns1"

Thank you for the quick solution, I clearly have much to learn!