Hi there,
I have 2 queries which have a simple join in the data model.
My pivot table uses data from both.
I wanted to add a calculated field to the pivot however the option in Excel is greyed out. I am using google to try understand why, but a simple layman's answer and solution is had to come by.
What I would like to understand is if I can do this in Power Query itself rather as an easier solution? I.e., am I able to bring the data in from one query to use with an existing field in a second query and add a calculated column in that second query power query window?
Eg:
Total stock - is calculated in Query1
Total adjustments is calculated in Query2
What process do I use to calculate the the Adjustments as a % of the Total Stock in either one of these query windows so the field is available for me to use in a pivot?
The underlying source xlsx sheets for both these queries will be updated weekly.
Many thanks
Paul
I am reviewing exercise 3.04 in the Power Query course to see if that has the solution to my issue above.
Hi Paul,
I've moved this thread to our Power Query course members forum so that I don't miss it again.
Once you load your data to the data model, the regular PivotTable features like calculated columns are no longer available. In Power Pivot we write measures with DAX functions.
That said, you can perform this calculation in Power Query and it's likely to be more efficient too. I'd start by merging the tables (lesson 4.06) to bring in the columns you need for your calculation. Then you can add the custom column in Power Query (lesson 4.14) that calculates the percentage and delete any columns you no longer need, before loading the data to the data model.
I hope that points you in the right direction. If you get stuck, come back with a mock-up Excel file where we can provide you with an example.
Mynda
