Trying to Combine Two Queries Which Have Common Columns

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

DateCampaign nameAd group nameAccount Sign Up (All conversions)Business (All conversions)Home (All conversions)Account Sign Up (All conversion value)Business (All conversion value)Home (All conversion value)
07/02/2017Brand - New VisitorsBrand - Exact400400
07/02/2017Brand - New VisitorsBrand - Other200200
07/02/2017BulkBulk SMS200200
07/02/2017BulkGroup Text100100
07/02/2017BusinessCompany Text100100
07/02/2017BusinessBusiness Text100100
07/02/2017GenericText Service100100
07/02/2017GenericSend Text200200
07/02/2017GenericSend SMS100100
07/02/2017GenericSMS Alert100100

<tbody>
</tbody>

Table 2

DateCampaign nameAd group nameCostImpressionsClicks
01/06/2017BulkBulk SMS279.2114510
14/06/2017BulkBulk SMS276.811868
06/03/2018BulkBulk SMS273.261329
06/12/2017BulkBulk SMS262.6615712
06/06/2017BulkBulk SMS261.291899
28/04/2017BulkBulk SMS252.3212812
04/12/2017BulkBulk SMS251.191078
25/04/2017BulkBulk SMS233.3313910
05/06/2017BulkBulk SMS231.692087
17/05/2017BulkBulk SMS229.7414712
09/02/2018GenericSend SMS224.0531220

<tbody>
</tbody>

Desired Outcome

DateCampaign nameAd group nameCostImpressionsClicksAccount Sign Up (All conversions)Business (All conversions)Home (All conversions)Account Sign Up (All conversion value)Business (All conversion value)Home (All conversion value)
01/06/2017BulkBulk SMS279.2114510400400
14/06/2017BulkBulk SMS276.811868200200
06/03/2018BulkBulk SMS273.261329200200
06/12/2017BulkBulk SMS262.6615712100100
06/06/2017BulkBulk SMS261.291899100100
28/04/2017BulkBulk SMS252.3212812100100
04/12/2017BulkBulk SMS251.191078100100
25/04/2017BulkBulk SMS233.3313910200200
05/06/2017BulkBulk SMS231.692087100100
17/05/2017BulkBulk SMS229.7414712100100

<tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

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