Power Query Problems

Build Multi-Location Sales Reports in Excel and Power BI

Phi Hung VoPhi Hung Vo Nov 14, 2026 870 views

Question details

Create filterable sales reports from QuickBooks exports for five restaurant locations.

Build Multi-Location Sales Reports in Excel or Power BI
Product
Microsoft Excel, Power Query, Power Pivot, and Power BI
Device & OS
not provided
Scenario
Analyze more than 10,000 exported sales rows by week, weekday, location, and sales type, using totals, averages, charts, and user-selected filters.
Observed behavior
The desired reports should consolidate the restaurant data and make comparisons across locations, dates, and sales categories easy.
Before you start

Check that each QuickBooks export contains a date, location, sales category, and numeric sales amount, and use consistent column names across exports. Decide whether missing category entries mean zero sales or unavailable data before calculating daily averages.

Solution 1Recommended

Build an Excel dashboard with Power Query and PivotTables

Use Excel to clean repeat exports and create filterable sales summaries and charts.

Power Query can repeat the import and cleaning steps when new exports are available. Power Pivot and the Data Model are useful when you need relationships between tables; their availability depends on your Excel edition.

A PivotTable average of transaction rows is not necessarily an average of daily sales. For an average daily total, first aggregate sales by date and the relevant location and category, then average those daily totals. Include missing days as zero only if that matches your reporting rules.

1
Import the exports

In Excel, go to Data > Get Data > From File and choose the appropriate source. If similarly structured exports are stored together, use From Folder to combine them; ensure each row identifies its restaurant location.

2
Clean and add date fields

In the Power Query Editor, set the date and sales amount data types, standardize location and category names, and use Add Column > Date to create weekday and week fields. Include a year field so weeks from different years can be distinguished.

3
Load the data

Select Home > Close & Load or Close & Load To. If you need relationships with other tables and your Excel edition supports it, choose the option to add the data to the Data Model.

4
Create the summaries

Select Insert > PivotTable and place location, weekday, week, or sales category in Rows or Columns. Add the sales amount to Values and use Value Field Settings to select Sum or Average as appropriate.

5
Add charts and filters

Select a PivotTable, then use PivotTable Analyze > PivotChart and Insert Slicer to add visualizations and filters for fields such as location and sales type. Use Data > Refresh All after updating the source exports.

Build an Excel dashboard with Power Query and PivotTables
Account for missing days: A missing category entry does not automatically appear as a zero in a PivotTable. If missing days should count as zero, create the required date, location, and category combinations before calculating daily averages.
Sales reporting in WPS Office

Create filterable restaurant sales reports in WPS Spreadsheets

Open the QuickBooks Excel exports in WPS Spreadsheets and use PivotTables, charts, and filters to summarize sales by location, date, and category.

  1. 1. Open the sales export: In WPS Spreadsheets, select Open and choose the QuickBooks Excel file. Check that dates, locations, categories, and sales amounts have consistent column headers.
  2. 2. Prepare the data: Combine similarly structured exports into one table and add year, week, and weekday columns as needed. For daily averages, first summarize sales by date, location, and category.
  3. 3. Create a PivotTable: Select the data range, choose Insert > PivotTable, and place location, week, or category fields in the report. Add sales amounts to Values and choose the appropriate summary calculation.
  4. 4. Visualize and filter results: Select the summary and use Insert > Chart to create a chart. Use PivotTable filters or worksheet filters to focus on the locations and sales types you want to compare.
Summarize restaurant sales with PivotTables and charts.Filter reports by location, week, weekday, or sales type.Open and save Microsoft Excel XLSX files.Use a familiar spreadsheet interface for reviewing exported sales data.
microsoft office alternative - wps office

Frequently Asked Questions

Can Excel handle more than 10,000 sales rows?

Yes. More than 10,000 rows is within Excel's worksheet limit. Use Data > Get Data to prepare the exports and Insert > PivotTable to summarize them.

How do I compare the same weekday across several weeks?

Add weekday, week, and year fields in Power Query using Add Column > Date. In a PivotTable, place weekday and week in Rows or Columns and add the sales amount to Values.

Will a PivotTable average show average daily sales?

Not necessarily. If each row is a transaction, Average calculates the average transaction amount. Aggregate by date and the relevant location and category first, then average the daily totals.

How do I update the report when new QuickBooks exports arrive?

Add or replace the source files in the location used by your Power Query connection, then select Data > Refresh All in Excel. Keep the export columns consistent so the query can process them.

Should I use Access instead of Excel for these reports?

Excel is a practical starting point for these exports and supports Power Query, PivotTables, and charts. Access may be useful if you need a relational database for storing or managing the data, but it is not required to build the reports.