Power Query - add custom formula to data set

Deonvg

New Member
Joined
Feb 4, 2010
Messages
27
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I would like to add a custom formula (extra column) in Power Query as part of an existing data set where I combine 3 files which contain a specific year's data by month into 1 pivot table. I cannot combine them into a excel data table as the data is currently at 2.5million rows for 2021, 2022 and YTD 2023. Each file is about 285mb that i combine using Power query.
The formula I would like to add is when I have the data in the pivot it automatically calculates the differences for i.e March YTD 2023 vs March YTD 2022 or April YTD 2023 vs April YTD 2022, this should change based on month selection or even year selection, if the selection changes to 2022 vs 2021 then it should calculate accordingly.

Apologies I cannot use the XL2BB as XLS blocks the Add-in due to an IT security setting in my company, seeing it doesn't see it as a Trusted Add-in. Hopefully the screenshots won't be a big frustration. Let me know!

I have added screenshots with an example of the Power query (very small portion of data set). I would like to add a custom formula in the Power Query to calculate the movement or growth in ABS between 2023 and 2022. 2nd screenshot of the pivot that will have the additional "field" with the custom formula.

I also unfortunately cannot add a formula field directly in the Pivot itself as I had to expand the fields as there are too many to show on a standard pivot and thus the "calculated field is greyed out.

I hope someone can help based on screenshots, once again apologies for not being able to add a file.
 

Attachments

  • Pivot Table.jpg
    Pivot Table.jpg
    44 KB · Views: 20
  • Power Query Dataset example.jpg
    Power Query Dataset example.jpg
    149.7 KB · Views: 18
Last edited:

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
You will need to change the
Power Query:
in 
#"Changed Type"
to . . . ) ,
Code:
addedColumn = Table.AddColumn(#"Changed Type", "Column Header/Name", each (some kind of calculation or condition))
in 
    addedColumn
Table.
 
Upvote 0

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top