Power QueryExercises

Combine marketing exports from several ad platforms in Power Query

Use case · By Tahir Nas · Examples run in our simulator · Updated

11 min read

To combine marketing exports from several ad platforms in Power Query, rename each platform's columns to one standard set using a mapping table, tag each row with its platform, and append. Then fix types per platform (date formats and thousands separators differ), remove totals rows, and calculate ratios such as CTR and CPC from summed counts, never by averaging the platforms' own ratios.

Why a plain append doesn't work

Each ad platform exports performance data in its own layout. The figures are the same kind of thing (date, campaign, impressions, clicks, spend), but the column names, date formats and number formatting differ, and they change when a platform updates its reporting. The example below uses two made-up exports, labelled "Search" and "Social", with the kinds of differences real exports have. Use the headers from your own files.

Microsoft's article on appending queries explains that an append matches columns by header name, not by position, and fills null where a table lacks a column. So appending these two exports as they are produces a wide, half-empty table:

let
    Search = #table({"Day", "Campaign", "Impr.", "Clicks", "Cost"}, {{"10/01/2026", "Autumn sale", "12,400", "310", "186.50"}}),
    Social = #table({"Reporting starts", "Campaign name", "Impressions", "Link clicks", "Amount spent (GBP)"}, {{"01/10/2026", "Autumn sale", 30500, 240, 120}}),
    Combined = Table.Combine({Search, Social})
in
    Combined

Result

DayCampaignImpr.ClicksCostReporting startsCampaign nameImpressionsLink clicksAmount spent (GBP)
10/01/2026Autumn sale12,400310186.50nullnullnullnullnull
nullnullnullnullnull01/10/2026Autumn sale30500240120

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The result has ten columns and two rows, with each platform's values in its own set of columns and null everywhere else. Not one column is filled on both rows: Search's "Campaign" and Social's "Campaign name" are separate columns, and so are "Clicks" and "Link clicks". Nothing errors, which is why this mistake survives into reports. The guide on merge vs append covers when an append is the right operation; here it is, but only after the columns agree.

Rename every platform to one standard set of columns

Put the renames in a table with three columns: the platform, the column name in its export, and the standard name. Then write one function that renames, keeps only the standard columns and tags the platform:

let
    Search = #table({"Day", "Campaign", "Impr.", "Clicks", "Cost"},
        {{"10/01/2026", "Autumn sale", "12,400", "310", "186.50"}, {"10/02/2026", "Autumn sale", "400", "40", "22.00"}, {"Total", null, "12,800", "350", "208.50"}}),
    Social = #table({"Reporting starts", "Campaign name", "Impressions", "Link clicks", "Amount spent (GBP)"},
        {{"01/10/2026", "Autumn sale", 30500, 240, 120}, {"02/10/2026", "Autumn sale", 28750, 198, 115.4}}),
    Names = #table(type table [Platform = text, From = text, To = text], {
        {"Search", "Day", "Date"}, {"Search", "Impr.", "Impressions"}, {"Search", "Cost", "Spend"},
        {"Social", "Reporting starts", "Date"}, {"Social", "Campaign name", "Campaign"}, {"Social", "Link clicks", "Clicks"}, {"Social", "Amount spent (GBP)", "Spend"}}),
    Standard = {"Date", "Campaign", "Impressions", "Clicks", "Spend"},
    Standardise = (t as table, platform as text) as table =>
        let
            Pairs = Table.ToRows(Table.SelectColumns(Table.SelectRows(Names, each [Platform] = platform), {"From", "To"})),
            Renamed = Table.RenameColumns(t, Pairs, MissingField.Ignore),
            Kept = Table.SelectColumns(Renamed, Standard, MissingField.UseNull),
            Tagged = Table.AddColumn(Kept, "Platform", each platform, type text)
        in
            Tagged,
    Combined = Table.Combine({Standardise(Search, "Search"), Standardise(Social, "Social")})
in
    Combined

Result

DateCampaignImpressionsClicksSpendPlatform
10/01/2026Autumn sale12,400310186.50Search
10/02/2026Autumn sale4004022.00Search
Totalnull12,800350208.50Search
01/10/2026Autumn sale30500240120Social
02/10/2026Autumn sale28750198115.4Social

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Now there are six columns (Date, Campaign, Impressions, Clicks, Spend, Platform) and five rows: three from Search, including its "Total" row, and two from Social. The values are still text in the Search rows; types come next.

Three options in the function do the defensive work:

  • MissingField.Ignore in Table.RenameColumns skips a rename whose source column isn't in this export. Without it, the documented behaviour is an error for any missing column, and one mapping row for an old header would break every refresh.
  • MissingField.UseNull in Table.SelectColumns creates a column of nulls if a standard column is missing, so every platform's table has the same shape before the append.
  • The Platform column records where each row came from, which you'll need for the type fixes and for reporting.

When a platform renames a column, the fix is one new row in the Names table. The same idea for a single source is covered in handling changing column headers. That table can live on a settings sheet in the workbook, next to the files it describes. The lesson on appending many tables and tagging the source and the guide to mapping tables instead of nested if cover both halves of this pattern.

In a real workbook each export would come from a file, for example with Csv.Document(File.Contents(path)) or a folder query, and the Standardise function would be applied to each one. The lesson on combining files from a folder shows that part.

Fix dates and numbers per platform

After renaming, the columns agree on names but not on content. In this sample, Search exports month-first dates (10/01/2026 is 1 October) and numbers as text with comma thousands separators; Social exports day-first dates and real numbers. A single "Change type" step would read one platform's dates wrongly without raising an error.

Keep each platform's date culture in a small table and use it when converting. Date.FromText takes a Culture option that controls the default format when no explicit format is given:

let
    Combined = #table(type table [Date = text, Campaign = text, Impressions = any, Clicks = any, Spend = any, Platform = text], {
        {"10/01/2026", "Autumn sale", "12,400", "310", "186.50", "Search"},
        {"10/02/2026", "Autumn sale", "400", "40", "22.00", "Search"},
        {"Total", null, "12,800", "350", "208.50", "Search"},
        {"01/10/2026", "Autumn sale", 30500, 240, 120, "Social"},
        {"02/10/2026", "Autumn sale", 28750, 198, 115.4, "Social"}}),
    Cultures = #table(type table [Platform = text, Culture = text], {{"Search", "en-US"}, {"Social", "en-GB"}}),
    NoTotals = Table.SelectRows(Combined, each [Date] <> "Total"),
    ToNumber = (v) => if v is number then v else Number.FromText(v, "en-US"),
    Typed = Table.TransformColumns(NoTotals, {{"Impressions", ToNumber, type number}, {"Clicks", ToNumber, type number}, {"Spend", ToNumber, type number}}),
    WithDay = Table.AddColumn(Typed, "Day", each Date.FromText([Date], [Culture = Cultures{[Platform = [Platform]]}[Culture]]), type date),
    Result = Table.RemoveColumns(WithDay, {"Date"})
in
    Result

Result

CampaignImpressionsClicksSpendPlatformDay
Autumn sale12400310186.5Search2026-10-01
Autumn sale4004022Search2026-10-02
Autumn sale30500240120Social2026-10-01
Autumn sale28750198115.4Social2026-10-02

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Four rows remain, all dated 1 or 2 October 2026, with numeric impressions, clicks and spend. The Search "Total" row is gone; left in, it would double the platform's totals the moment anyone summed the column. Cultures{[Platform = [Platform]]} looks up the row for the current platform in the culture table, which Expression.Error: The key didn't match any rows will report if a new platform appears without a culture row. That error is useful: it stops the refresh before dates are misread.

Numbers need the same care. "12,400" is twelve thousand four hundred in en-US but twelve point four in a culture that uses the comma as its decimal separator. The guide to changing type using locale covers both cases in detail, and the exercise Convert day-first dates with the right locale practises it.

Currency is the other trap. If platforms bill in different currencies, convert spend to one reporting currency before combining figures, using the rate your finance team uses for the period, and keep the original currency in a column so the conversion can be checked. The post on currency conversion with an exchange-rate table shows how to match each row to its rate.

Calculate CTR and CPC after combining

With one tidy table, the reporting step is a group by platform (or campaign, or week). Ratios must be calculated from the summed parts. Here is the difference on the same four days of data:

let
    Daily = #table(type table [Platform = text, Day = date, Impressions = number, Clicks = number, Spend = number], {
        {"Search", #date(2026, 10, 1), 12400, 310, 186.5}, {"Search", #date(2026, 10, 2), 400, 40, 22},
        {"Social", #date(2026, 10, 1), 30500, 240, 120}, {"Social", #date(2026, 10, 2), 28750, 198, 115.4}}),
    WithCtr = Table.AddColumn(Daily, "Daily CTR", each [Clicks] / [Impressions], type number),
    ByPlatform = Table.Group(WithCtr, {"Platform"}, {
        {"Impressions", each List.Sum([Impressions]), type number},
        {"Clicks", each List.Sum([Clicks]), type number},
        {"Spend", each List.Sum([Spend]), type number},
        {"Average of daily CTR", each Number.Round(List.Average([Daily CTR]), 4), type number}}),
    Ctr = Table.AddColumn(ByPlatform, "CTR", each Number.Round([Clicks] / [Impressions], 4), type number),
    Cpc = Table.AddColumn(Ctr, "CPC", each Number.Round([Spend] / [Clicks], 2), type number)
in
    Cpc

Result

PlatformImpressionsClicksSpendAverage of daily CTRCTRCPC
Search12800350208.50.06250.02730.6
Social59250438235.40.00740.00740.54

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

For Search, the average of the two daily CTRs is 0.0625 (6.25%), but the true CTR is 0.0273 (2.73%): 350 clicks from 12,800 impressions. The second day had only 400 impressions and a high click rate, and a simple average gives that small day the same weight as the large one. For Social the two figures agree (0.0074), only because its two days had similar volumes. CPC follows the same rule: 208.50 spend over 350 clicks is 0.60 for Search, and 0.54 for Social.

The same applies to any figure that a platform reports as a ratio: CTR, CPC, CPM, conversion rate, return on ad spend. Import the counts and amounts, not the platform's ratio columns, and divide after grouping. The Group By recipes guide shows more aggregation patterns.

Remember too that each platform defines its own metrics: what counts as a click, or how a conversion is attributed, can differ from one platform to the next. A combined table makes the numbers comparable in shape, not in meaning. Read each platform's metric definitions and say in the report which metric was taken from each one.

Check the mapping on every refresh

The weakest point in this design is the mapping table. If a platform renames "Cost" to "Spend amount", MissingField.Ignore skips the old rename, MissingField.UseNull fills Spend with nulls, and the report shows no spend for that platform without an error. A check after combining catches it:

let
    Combined = #table(type table [Platform = text, Campaign = text, Clicks = nullable number, Spend = nullable number], {
        {"Search", "Autumn sale", 310, 186.5}, {"Social", "Autumn sale", 240, null}, {"Social", "Brand", 198, null}}),
    Required = {"Clicks", "Spend"},
    Platforms = List.Distinct(Combined[Platform]),
    Empty = List.Combine(List.Transform(Platforms, (p) =>
        let rows = Table.SelectRows(Combined, each [Platform] = p)
        in List.Transform(List.Select(Required, (c) => List.NonNullCount(Table.Column(rows, c)) = 0), each p & ": " & _))),
    Checked = if List.IsEmpty(Empty) then Combined
        else error Error.Record("Empty column after renaming", "Check the column mapping for these platforms", Text.Combine(Empty, ", "))
in
    Checked

Result: an error

Empty column after renaming: Check the column mapping for these platforms

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Social's Spend column is entirely null, so the refresh stops with "Empty column after renaming", and the error detail names Social: Spend. The lesson on missing columns and MissingField options explains when to ignore a missing column and when to fail, and the lesson on validating data and failing loudly covers custom errors like this one.

A layout that survives the next export change

PieceWhere it livesWho updates it
Column mapping (platform, from, to)Table on a settings sheetWhoever downloads the exports
Date culture per platformSame settings sheetSame person
Standardise functionOne query, used for every platformQuery owner
Combined, typed tableStaging query, not loadedNobody: it's derived
Report tables (by platform, campaign, week)Reference the staging queryQuery owner

Keeping the combined table as a staging query that other queries reference means every report uses the same cleaned data. The lesson on referencing other queries for staging and reuse explains how to set that up.

Practise the steps

Two exercises cover the core moves: Append store and web sales with different column names and Rename columns from a mapping table. After those, Accept both old and new names for a column practises the "platform renamed a header" problem. On Power Query Exercises each answer is checked in the browser against hidden variants of the data, so a query that only works on the sample file is caught.

Frequently asked questions

Can Power Query connect to the ad platforms directly?

Sometimes. Microsoft's list of Power Query connectors shows which connectors exist and whether each is available in Excel, Power BI semantic models and Power Query Online; many are available in Power BI but not in Excel. Check the list for the platforms you use. Exports in CSV or Excel work everywhere, and the combining logic in this post is the same whichever way the data arrives.

What if one platform reports by week and another by day?

Combine at the coarser grain. Add a week-start column to the daily data with Date.StartOfWeek, group the daily rows to weeks, then append. If a platform labels weeks by number instead, check which rule it uses; ISO weeks vs Date.WeekOfYear explains the difference. Never append daily and weekly rows into one table without a grain column, or sums will mix the two.

How do I match campaigns across platforms?

Use a naming convention with a shared code (for example AUT26- at the start of every campaign name) and extract it with a split or Text.BeforeDelimiter. Where names don't follow the convention, a mapping table from platform campaign name to reporting campaign is more reliable than text matching.

Sources

About the author

Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.

Keep reading

Practise Power Query in the browser with graded exercises checked against fresh data.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.