Help with Power Query and Power Pivot

Below some suggestions:

 

1. Create two separate tables in Excel for Target Sales Table

2. Actual Sales Table

 

• 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

1. Select each table and go to Data > From Table/Range to load them into Power Query.
2. 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

1. Go to Power Pivot > Manage to open the data model.
2. 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.
3. 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