Hi Arun,

This is covered in session 7.01. Let me know if you get stuck after watching the tutorial and attempting it yourself. It will help if you can include your Excel file so we can see where things went arry.

Mynda

Hi Mynda,

Thanks for your reply. 7.01 is more related to filtering the rows. My issue is to apply parameter in calculated column like current year column minus previous year column. 

I have attached a sample file. I have created parameter for current year and previous year but when I mention parameter in calculated column then it shows error.

Please have a look.

Kind Regards

Arun

Hi Arun,

Thanks for clarifying and sharing your file. I'm a bit concerned because this data isn't in a tabular format i.e. you should have 3 columns in your table:

1. Period

2. Year

3. Amount

What are your plans for this data after Power Query? If you want to analyse it in a PivotTable or Power Pivot you won't be able to. Ideally this calculation would be done in Power Pivot using the time intelligence functions, as opposed to Power Query.

That's not to say it can't be done, but before we go down that path, I want to make sure you're certain you won't need this data for any further analysis?

Mynda

Hi Mynda,

Basically I need dynamic calculated column (variance between CY and PY) in power query, so if I change period in excel file cell, refresh query and variance column is updated.

Data format is correct. Period (1, 2, 3, 4.....200) represent future period. In sample file I included only 8 rows. Or you can say this is pivoted data set. Hard coded calculation is possible in both Power Pivot and Power Query but I am trying to make it Dynamic Like you did for filtering rows. 

Please advise.

Kind Regards

Arun

Hi Arun,

Dynamic calculations like this are possible in both Power Query and Power Pivot. My concern is that you shouldn't be doing a calculation like this in Power Query. Ideally this is something Power Pivot does. If you do it in Power Query then you can't put this data into Power Pivot, or use it in a PivotTable later on.

I understand what you want, but I want to make sure you don't ever need to use this data in Power Pivot or regular PivotTables, because if you do then Power Query isn't the place for the calculation.

So, before we go down the Power Query path, please confirm you never want to put this data into Power Pivot or a PivotTable.

Thanks,

Mynda

Hi Mynda,

Data will not be used in Power Pivot or in a Pivot table.

Please advise.

Kind Regards

Arun

Hi Arun,

Please see attached.

Mynda

Thank you so much Mynda.

You are a precious gem!!!!!!!!!!!

Kind Regards

Arun