Forum Discussion
macy517
Jul 02, 2025Copper Contributor
Help with Power Query and Power Pivot
First time trying to use these features. Have googled and read/watched numerous tutorials but as they dont really match my data set up I just cant follow how to get this right. Hopefully someone ca...
Kidd_Ip
Jul 04, 2025MVP
Below some suggestions:
1. Create two separate tables in Excel for Target Sales Table
|
Stream |
Month |
Value |
|
Dealers |
Jan 2025 |
100000 |
|
Government |
Jan 2025 |
80000 |
|
Direct |
Jan 2025 |
120000 |
|
... |
... |
... |
- Actual Sales Table
|
Stream |
Month |
Value |
|
Dealers |
Jan 2025 |
95000 |
|
Government |
Jan 2025 |
85000 |
|
Direct |
Jan 2025 |
110000 |
|
... |
... |
... |
- The Month column is in a consistent format (e.g., MMM YYYY or actual date).
- The Stream values are consistent across both tables.
2. Load Data into Power Query
- Select each table and go to Data > From Table/Range to load them into Power Query.
- In Power Query:
- Rename queries to TargetSales and ActualSales.
- Ensure data types are correct (e.g., Month as Date, Value as Decimal).
- Click Close & Load To… > Only Create Connection and check Add this data to the Data Model.
3: Combine in Power Pivot
- Go to Power Pivot > Manage to open the data model.
- Create a Calendar Table:
- In Excel, make a list of all months in your data.
- Load it into Power Query and then into the Data Model.
- Mark it as a Date Table in Power Pivot.
- Create relationships:
- Link TargetSales[Month] and ActualSales[Month] to Calendar[Month].
- Link TargetSales[Stream] and ActualSales[Stream] to a new Stream Table (a list of unique streams).
4: Create Measures in Power Pivot
In Power Pivot, create DAX measures:
Total Target := SUM(TargetSales[Value])
Total Actual := SUM(ActualSales[Value])
Variance := [Total Actual] - [Total Target]
Variance % := DIVIDE([Variance], [Total Target])
5: Visualize in PivotTables or Power BI
- Insert a PivotTable from the Data Model.
- Use Calendar[Month] and Stream[Stream] as rows/columns.
- Add your measures as values.
How Power Query and Power Pivot work together - Microsoft Support