Merging Queries including ALL Rows

TheMacroNoob

TheMacroNoob

Board Regular
Joined
Aug 5, 2022
Messages
52
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVW
3ActualBudgetDesired ResultMy Result
4
5GLAccountPropertyActualGLAccountPropertyBudgetGLAccountPropertyActualBudgetGLAccountPropertyActualBudget
60000-1000A107306080000-1000A10773990000-1000A1073060873990000-1000A107306087399
70000-1000A109267920000-1000A10916410000-1000A1092679216410000-1000A107306081641
80000-1000A11054360000-1000A11035980000-1000A110543635980000-1000A107306083598
90000-1000A32053330000-1001B10754660000-1000A320533300000-1000A109267927399
100000-1001B107169850000-1001B110890000-1001B1071698554660000-1000A109267921641
110000-1001B110208790000-1001B32043140000-1001B11020879890000-1000A109267923598
120000-1001B32078370000-1008C1079120000-1001B320783743140000-1000A11054367399
130000-1008C107145310000-1008C11030850000-1008C107145319120000-1000A11054361641
140000-1008C109402660000-1008C32064830000-1008C1094026600000-1000A11054363598
150000-1008C110502230000-1008C65837320000-1008C1105022330850000-1000A32053337399
160000-1008C32041650000-1009D1109810000-1008C320416564830000-1000A32053331641
170000-1008C658389580000-1009D32017700000-1008C6583895837320000-1000A32053333598
180000-1009D11009810000-1001B107169855466
190000-1009D320017700000-1001B1071698589
200000-1001B107169854314
210000-1001B110208795466
220000-1001B1102087989
230000-1001B110208794314
240000-1001B32078375466
250000-1001B320783789
260000-1001B32078374314
270000-1008C10714531912
280000-1008C107145313085
290000-1008C107145316483
300000-1008C107145313732
310000-1008C10940266912
320000-1008C109402663085
330000-1008C109402666483
340000-1008C109402663732
350000-1008C11050223912
360000-1008C110502233085
370000-1008C110502236483
380000-1008C110502233732
390000-1008C3204165912
400000-1008C32041653085
410000-1008C32041656483
420000-1008C32041653732
430000-1008C65838958912
440000-1008C658389583085
450000-1008C658389586483
460000-1008C658389583732
47981
481770
49
50
Help
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Power Query: 
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"
 
Upvote 0
Solution
alansidman said:
Power Query: 
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"
Click to expand...
Thank you for the quick solution, I clearly have much to learn!
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Query merge not finding matches
Replies
2
Views
237
egspen2
E
W
  • Question Question
Merging two queries, error when one query has no data
Replies
1
Views
198
Matt Allington
M
P
  • Question Question
Power Query to Merge and Fill in gaps in data
Replies
7
Views
372
alex78
A
I
  • Question Question
Power Query Data types and excel time format issue
Replies
5
Views
467
Anand Sydney
A
W
  • Solved
Combining/Extract data from columns and row from merged to one column
Replies
7
Views
419
smozgur
smozgur

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top