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.
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
Last edited: