excelisfun - Excel Magic Trick 1436 Excel 2016: Combine 2 BIG Tables into 1 for PivotTable Report, No Power Pivot

Short Summary

Main recommendation: To combine two very large Excel tables into one for a PivotTable report without Power Pivot, use Power Query to append the tables, load the result into the Data Model, and build the PivotTable from there. The video notes that the Data Model is available in every version of Excel 2016, not only versions with Power Pivot, and that it can hold far more rows than a worksheet, up to 100 million.

Key Points

• The tables are too large to copy and paste into one sheet, with the first reaching 900,000 rows (via Control+Down Arrow) and the second almost a million rows.
• Both tables are imported with From Table into the Power Query Editor, and each is loaded only as a connection (not yet to the Data Model, which happens only for the combined query).
• Power Query's Combine Queries, then Append, joins the two tables into one query, named "All Together" in the video.
• The combined data (about 1.9 million records) is stored in the Data Model's columnar database, which compresses it efficiently, resulting in a smaller file size than storing the data in worksheet form.
• The PivotTable is created from the Data Model, showing product totals as an example.

Details Worth Noting

• Concrete instructions:
  1. Convert each data range to an Excel Table by clicking a single cell and pressing Control + T (or Insert > Table), then name each table in the Design tab (the video uses FSales1 and FSales2).
  2. On the Data ribbon tab, in the Get & Transform group, click From Table/Range for the first table to open the Power Query Editor.
  3. Check the column data types. In the example, the date column is changed to Date and the other types are left as they are. When asked whether to replace the existing step, click Yes.
  4. Click the Close & Load drop-down (not the main button) and choose Close & Load To. In the Import Data dialog, select only "Only Create Connection," then click OK.
  5. Repeat steps 2 to 4 for the second table.
  6. Go to Data > Combine Queries > Append, choose Append Two Tables, select the two queries (the order does not matter when the field names match), and click OK. Name the result (the video uses "All Together").
  7. Click Close & Load, then Close & Load To, choose "Only Create Connection," check "Add this data to the Data Model," and click OK.
  8. To build the PivotTable, go to Insert > PivotTable (Alt + N + V), keep the Data Model as the source, click OK, drag Product to Rows and Sales to Values, then right-click the values to set Number Formatting to Currency.
• The video suggests saving the file after loading data into the Data Model but before deleting the original tables; in the example, the file size went from 41 MB to 9 MB after the data model load (not from the deletion itself).
• The video notes that Power Pivot is needed to view the Data Model's contents directly, but the Data Model itself is in every version of Excel 2016.
• The presenter says the append can be done with three or more tables, not only two.
• If you use Power Pivot, the video suggests creating a measure in the Data Model rather than dragging Sales directly into Values.

Bottom Line

Power Query plus the Data Model lets Excel 2016 handle millions of rows that a worksheet cannot hold, so you can build a PivotTable from data too large to paste into one sheet.