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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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
2K
montex01
M
N
  • Question Question
Conditional Format for row based on value in cell
Replies
1
Views
589
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,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

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