Power Query Problems

Create a Dynamic Excel Dashboard with Power Query Calculations

Camila MilosovichCamila Milosovich Nov 18, 2026 870 views

Question details

Create a refreshable Excel dashboard showing weighted averages, daily currency balances, and converted values.

Create a Dynamic Excel Dashboard with Power Query Calculations
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
The user wants to summarize transaction data by date, currency, transaction type, and region and present the results in an interactive dashboard.
Observed behavior
The desired dashboard should update its calculations and visualizations when the source data is refreshed.
Before you start

Format the transaction data as an Excel table with dates, currencies, quantities, rates, amounts, transaction types, and regions. Prepare a separate exchange-rate table with dates, currency pairs, and clearly defined rate directions, and identify any opening balances.

Solution 1Recommended

Calculate and summarize transactions in Power Query

Use Power Query to prepare weighted rates, daily balances, and converted amounts for the dashboard.

A weighted average is the sum of quantity multiplied by rate divided by the sum of quantity. Keep currencies separate when calculating balances, and use the exchange rate applicable to each transaction or reporting date.

Confirm what the DL currency code represents and its conversion rule before using it. Do not assume that its rate direction matches the EUR-to-USD rate.

1
Import the tables

Select the transaction table and choose Data > From Table/Range. Repeat for the exchange-rate table, then check the date, text, and numeric data types in Power Query Editor.

2
Prepare weighted values

In the transaction query, choose Add Column > Custom Column and create a weighted-value column using [Quantity] * [Rate]. Use Home > Group By, select Advanced, and group by date, currency, transaction type, and region. Sum both Quantity and the weighted-value column.

3
Calculate the weighted average

Choose Add Column > Custom Column and divide the grouped weighted-value total by the grouped quantity total. Handle zero total quantity separately, for example with an if expression that returns null.

4
Create daily running balances

In a separate transaction query, use Home > Group By to sum signed amounts by date, currency, and region. Group the daily rows by currency and region using the All Rows operation. Within each nested table, sort by date, add a zero-based index, and calculate the cumulative sum of daily amounts through each index. Add the applicable opening balance, then expand the nested results.

5
Apply exchange rates

Use Home > Merge Queries to join the daily results to the exchange-rate table by date and the appropriate currency pair. Expand the rate column and use Add Column > Custom Column to convert EUR amounts to USD using the rate direction supplied. Check for unmatched dates and apply the confirmed rule for DL separately.

6
Load the results

Choose Home > Close & Load or Close & Load To to load the summarized queries into Excel tables or the Data Model for reporting.

Calculate and summarize transactions in Power Query
Check balance and rate rules: Use signed amounts consistently for inflows and outflows. If exchange rates are missing for some dates, establish a policy for those dates rather than silently treating missing rates as zero.
Free Microsoft Office alternative

Explore spreadsheet dashboards with WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Excel files, making it easier to move existing workbooks. It does not fix Microsoft Excel Power Query issues, so check your workbook's query and dashboard compatibility before migrating.

Work with spreadsheet tables and charts for dashboard reportingOpen and save Microsoft Excel XLSX filesUse a familiar interface for everyday spreadsheet tasksGet a lightweight office suite with a free option
microsoft office alternative - wps office

Frequently Asked Questions

How do I calculate a weighted average in Power Query?

Use Add Column > Custom Column to multiply quantity by rate. Then use Home > Group By to sum that result and the quantity for each group, and divide the two totals. Return null or another appropriate value when total quantity is zero.

How do I keep running balances separate for each currency?

First group signed transactions into daily totals by currency and any other balance dimension, such as region. Then group by those balance dimensions with All Rows, sort each nested table by date, and calculate its cumulative total separately.

Which exchange rate should I use to convert EUR to USD?

Use the rate applicable to the transaction or reporting date, according to your reporting rules. If the rate is quoted as USD per EUR, multiply the EUR amount by that rate; if it is quoted in the opposite direction, use the corresponding inverse calculation.

Why does my dashboard not include new transactions?

Make sure the new rows are inside the source Excel table, then choose Data > Refresh All. If the data comes from an external source, also check the query's source settings and refresh any connected PivotTables.

Can one slicer filter multiple PivotTables?

Often, yes. Select the slicer and open Report Connections to choose compatible PivotTables that share the same underlying source or Data Model.