Trying to Combine Two Queries Which Have Common Columns

J

JustVic

New Member
Joined
Mar 14, 2018
Messages
1
Hey everyone,

I hope you are able to help! I am using Excel 2016 + Windows 10.

I am trying to merge two web queries which have three common columns (Date, Campaign Name and Ad Group Name) please see below for the tables and desired outcome. I tried doing a full inner but I end duplicating the common columns.

Thank you in advance!




Table 1

[TABLE="width: 2258"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Campaign name[/TD]
[TD]Ad group name[/TD]
[TD]Account Sign Up (All conversions)[/TD]
[TD]Business (All conversions)[/TD]
[TD]Home (All conversions)[/TD]
[TD]Account Sign Up (All conversion value)[/TD]
[TD]Business (All conversion value)[/TD]
[TD]Home (All conversion value)[/TD]
[/TR]
[TR]
[TD="align: right"]07/02/2017[/TD]
[TD]Brand - New Visitors[/TD]
[TD]Brand - Exact[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]07/02/2017[/TD]
[TD]Brand - New Visitors[/TD]
[TD]Brand - Other[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]07/02/2017[/TD]
[TD]Bulk[/TD]
[TD]Bulk SMS[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]07/02/2017[/TD]
[TD]Bulk[/TD]
[TD]Group Text[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]07/02/2017[/TD]
[TD]Business[/TD]
[TD]Company Text[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]07/02/2017[/TD]
[TD]Business[/TD]
[TD]Business Text[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]07/02/2017[/TD]
[TD]Generic[/TD]
[TD]Text Service[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]07/02/2017[/TD]
[TD]Generic[/TD]
[TD]Send Text[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]07/02/2017[/TD]
[TD]Generic[/TD]
[TD]Send SMS[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]07/02/2017[/TD]
[TD]Generic[/TD]
[TD]SMS Alert[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

Table 2

[TABLE="width: 1034"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Campaign name[/TD]
[TD]Ad group name[/TD]
[TD]Cost[/TD]
[TD]Impressions[/TD]
[TD]Clicks[/TD]
[/TR]
[TR]
[TD="align: right"]01/06/2017[/TD]
[TD]Bulk[/TD]
[TD]Bulk SMS[/TD]
[TD="align: right"]279.21[/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]14/06/2017[/TD]
[TD]Bulk[/TD]
[TD]Bulk SMS[/TD]
[TD="align: right"]276.81[/TD]
[TD="align: right"]186[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]06/03/2018[/TD]
[TD]Bulk[/TD]
[TD]Bulk SMS[/TD]
[TD="align: right"]273.26[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]06/12/2017[/TD]
[TD]Bulk[/TD]
[TD]Bulk SMS[/TD]
[TD="align: right"]262.66[/TD]
[TD="align: right"]157[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]06/06/2017[/TD]
[TD]Bulk[/TD]
[TD]Bulk SMS[/TD]
[TD="align: right"]261.29[/TD]
[TD="align: right"]189[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]28/04/2017[/TD]
[TD]Bulk[/TD]
[TD]Bulk SMS[/TD]
[TD="align: right"]252.32[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]04/12/2017[/TD]
[TD]Bulk[/TD]
[TD]Bulk SMS[/TD]
[TD="align: right"]251.19[/TD]
[TD="align: right"]107[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]25/04/2017[/TD]
[TD]Bulk[/TD]
[TD]Bulk SMS[/TD]
[TD="align: right"]233.33[/TD]
[TD="align: right"]139[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]05/06/2017[/TD]
[TD]Bulk[/TD]
[TD]Bulk SMS[/TD]
[TD="align: right"]231.69[/TD]
[TD="align: right"]208[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]17/05/2017[/TD]
[TD]Bulk[/TD]
[TD]Bulk SMS[/TD]
[TD="align: right"]229.74[/TD]
[TD="align: right"]147[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]09/02/2018[/TD]
[TD]Generic[/TD]
[TD]Send SMS[/TD]
[TD="align: right"]224.05[/TD]
[TD="align: right"]312[/TD]
[TD="align: right"]20[/TD]
[/TR]
</tbody>[/TABLE]

Desired Outcome

[TABLE="width: 1034"]
<tbody>[TR]
[TD][TABLE="width: 1466"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Campaign name[/TD]
[TD]Ad group name[/TD]
[TD]Cost[/TD]
[TD]Impressions[/TD]
[TD]Clicks[/TD]
[TD]Account Sign Up (All conversions)[/TD]
[TD]Business (All conversions)[/TD]
[TD]Home (All conversions)[/TD]
[TD]Account Sign Up (All conversion value)[/TD]
[TD]Business (All conversion value)[/TD]
[TD]Home (All conversion value)[/TD]
[/TR]
[TR]
[TD="align: right"]01/06/2017[/TD]
[TD]Bulk[/TD]
[TD]Bulk SMS[/TD]
[TD="align: right"]279.21[/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]14/06/2017[/TD]
[TD]Bulk[/TD]
[TD]Bulk SMS[/TD]
[TD="align: right"]276.81[/TD]
[TD="align: right"]186[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]06/03/2018[/TD]
[TD]Bulk[/TD]
[TD]Bulk SMS[/TD]
[TD="align: right"]273.26[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]06/12/2017[/TD]
[TD]Bulk[/TD]
[TD]Bulk SMS[/TD]
[TD="align: right"]262.66[/TD]
[TD="align: right"]157[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]06/06/2017[/TD]
[TD]Bulk[/TD]
[TD]Bulk SMS[/TD]
[TD="align: right"]261.29[/TD]
[TD="align: right"]189[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]28/04/2017[/TD]
[TD]Bulk[/TD]
[TD]Bulk SMS[/TD]
[TD="align: right"]252.32[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]04/12/2017[/TD]
[TD]Bulk[/TD]
[TD]Bulk SMS[/TD]
[TD="align: right"]251.19[/TD]
[TD="align: right"]107[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]25/04/2017[/TD]
[TD]Bulk[/TD]
[TD]Bulk SMS[/TD]
[TD="align: right"]233.33[/TD]
[TD="align: right"]139[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]05/06/2017[/TD]
[TD]Bulk[/TD]
[TD]Bulk SMS[/TD]
[TD="align: right"]231.69[/TD]
[TD="align: right"]208[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]17/05/2017[/TD]
[TD]Bulk[/TD]
[TD]Bulk SMS[/TD]
[TD="align: right"]229.74[/TD]
[TD="align: right"]147[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
The powerquery / M language version would be

Code: 
let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Table1 = Table.TransformColumnTypes(Source1,{{"Date", type date}, {"Campaign name", type text}, {"Ad group name", type text}, {"Account Sign Up (All conversions)", Int64.Type}, {"Business (All conversions)", Int64.Type}, {"Home (All conversions)", Int64.Type}, {"Account Sign Up (All conversion value)", Int64.Type}, {"Business (All conversion value)", Int64.Type}, {"Home (All conversion value)", Int64.Type}}),
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Table2 = Table.TransformColumnTypes(Source2,{{"Date", type date}, {"Campaign name", type text}, {"Ad group name", type text}, {"Cost", type number}, {"Impressions", Int64.Type}, {"Clicks", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(Table2,{"Date", "Campaign name", "Ad group name"},Table1,{"Date", "Campaign name", "Ad group name"},"Table1",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Account Sign Up (All conversions)", "Business (All conversions)", "Home (All conversions)", "Account Sign Up (All conversion value)", "Business (All conversion value)", "Home (All conversion value)"}, {"Account Sign Up (All conversions)", "Business (All conversions)", "Home (All conversions)", "Account Sign Up (All conversion value)", "Business (All conversion value)", "Home (All conversion value)"})
in
    #"Expanded Table1"
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question Question
PowerQuery Column Generation
Replies
8
Views
2K
sandy666
sandy666
Rob_010101
  • Question Question
Filter Data?
2
Replies
16
Views
1K
kevin9999
K
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
N
  • Question Question
Conditional Format for row based on value in cell
Replies
1
Views
583
Gerald Higgins
Gerald Higgins
U
  • Question Question
Select Case with criteria for two different columns
Replies
8
Views
1K
RCBricker
R

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

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