Forum Discussion
Creating a Percent Change Column in Power Query
I want to add a column for percent change between Fiscal Years within each ITEM_DESCRIPTION. I have tried so many things and can't figure this out. I am getting so annoyed. I can't believe Power Query doesn't have a Previous Row function built in by!! Let alone a Percent Change function that allows you to choose the parameters.
**The Percent Change column in bold is what I am trying to achieve, I just put it there so you could see what I was trying to do.
In Power Query I used the Group By and Index functions to get the index to restart for each ITEM_DESCRIPTION, although I don't know if that was the right thing to do or even helpful.
| ITEM_DESCRIPTION | Fiscal Year | Index | Avg. Awarded PPU | Percent Change |
| CONCRETE FOOTING (MONOTUBE) | 2021 | 1 | 4429 | 0 |
| CONCRETE FOOTING (MONOTUBE) | 2022 | 2 | 4396 | -33 |
| CONCRETE FOOTING (MONOTUBE) | 2023 | 3 | 2209 | -2187 |
| CONCRETE FOOTING (MONOTUBE) | 2024 | 4 | 1270 | -939 |
| CONCRETE FOOTING (MONOTUBE) | 2025 | 5 | 1172 | -98 |
| CONCRETE FOOTING (OVERHEAD SHAFT) | 2022 | 2 | 1504 | 0 |
| CONCRETE FOOTING (OVERHEAD SHAFT) | 2023 | 3 | 1162 | -342 |
| CONCRETE FOOTING (OVERHEAD SHAFT) | 2024 | 4 | 1020 | -142 |
| CONCRETE FOOTING (OVERHEAD SHAFT) | 2025 | 5 | 2608 | 1588 |
| EXTRUDED PANEL | 2021 | 1 | 1193 | 0 |
| EXTRUDED PANEL | 2022 | 2 | 3714 | 2521 |
| EXTRUDED PANEL | 2023 | 3 | 2378 | -1336 |
| EXTRUDED PANEL | 2024 | 4 | 3903 | 1525 |
| EXTRUDED PANEL | 2025 | 5 | 171 | -3732 |
| MEDIAN BARRIER FOOTING (SHAFT) | 2023 | 3 | 2498 | 0 |
| MEDIAN BARRIER FOOTING (SHAFT) | 2024 | 4 | 841 | -1657 |
| MEDIAN BARRIER FOOTING (SHAFT) | 2025 | 5 | 3226 | 2385 |
| SIGN PANEL OVERLAY | 2021 | 1 | 4754 | 0 |
| SIGN PANEL OVERLAY | 2022 | 2 | 940 | -3814 |
| SIGN PANEL OVERLAY | 2023 | 3 | 3887 | 2947 |
| SIGN PANEL OVERLAY | 2024 | 4 | 2247 | -1640 |
| SIGN PANEL OVERLAY | 2025 | 5 | 2820 | 573 |
| STRUCTURAL STEEL - BRIDGE MOUNTED TRUSS | 2022 | 2 | 2955 | 0 |
| STRUCTURAL STEEL - BRIDGE MOUNTED TRUSS | 2023 | 3 | 4037 | 1082 |
| STRUCTURAL STEEL - BRIDGE MOUNTED TRUSS | 2024 | 4 | 3755 | -282 |
| STRUCTURAL STEEL - BRIDGE MOUNTED TRUSS | 2025 | 5 | 1619 | -2136 |
| STRUCTURAL STEEL - I-BEAM SIGN | 2022 | 2 | 2875 | 0 |
| STRUCTURAL STEEL - I-BEAM SIGN | 2023 | 3 | 4216 | 1341 |
| STRUCTURAL STEEL - I-BEAM SIGN | 2024 | 4 | 1219 | -2997 |
| STRUCTURAL STEEL - I-BEAM SIGN | 2025 | 5 | 920 | -299 |
| STRUCTURAL STEEL - MONOTUBE SIGN | 2021 | 1 | 3415 | 0 |
| STRUCTURAL STEEL - MONOTUBE SIGN | 2022 | 2 | 3731 | 316 |
| STRUCTURAL STEEL - MONOTUBE SIGN | 2023 | 3 | 3598 | -133 |
| STRUCTURAL STEEL - MONOTUBE SIGN | 2024 | 4 | 2313 | -1285 |
| STRUCTURAL STEEL - MONOTUBE SIGN | 2025 | 5 | 4129 | 1816 |
| STRUCTURAL STEEL - OVERHEAD SIGN POST | 2022 | 2 | 2862 | 0 |
| STRUCTURAL STEEL - OVERHEAD SIGN POST | 2023 | 3 | 672 | -2190 |
| STRUCTURAL STEEL - OVERHEAD SIGN POST | 2024 | 4 | 2238 | 1566 |
| STRUCTURAL STEEL - OVERHEAD SIGN POST | 2025 | 5 | 2590 | 352 |
| STRUCTURAL STEEL - OVERHEAD SIGN TRUSS | 2022 | 2 | 352 | 0 |
| STRUCTURAL STEEL - OVERHEAD SIGN TRUSS | 2023 | 3 | 4203 | 3851 |
| STRUCTURAL STEEL - OVERHEAD SIGN TRUSS | 2024 | 4 | 4610 | 407 |
| STRUCTURAL STEEL - OVERHEAD SIGN TRUSS | 2025 | 5 | 1933 | -2677 |
| STRUCTURAL STEEL - PANEL MOUNTING POST | 2022 | 2 | 4939 | 0 |
| STRUCTURAL STEEL - PANEL MOUNTING POST | 2023 | 3 | 3416 | -1523 |
| STRUCTURAL STEEL - PANEL MOUNTING POST | 2024 | 4 | 2892 | -524 |
| STRUCTURAL STEEL - PANEL MOUNTING POST | 2025 | 5 | 2418 | -474 |
Can anyone please help figure this out in the simplist way possible!!! Either in Power Query or with DAX outside of Power Query.
Hi,
This calculated column formula works
Column = Data[Avg. Awarded PPU]-COALESCE(LOOKUPVALUE(Data[Avg. Awarded PPU],Data[Fiscal Year],CALCULATE(MAX(Data[Fiscal Year]),FILTER(Data,Data[ITEM_DESCRIPTION]=EARLIER(Data[ITEM_DESCRIPTION])&&Data[Fiscal Year]<EARLIER(Data[Fiscal Year]))),Data[ITEM_DESCRIPTION],Data[ITEM_DESCRIPTION]),Data[Avg. Awarded PPU])Hope this helps.
4 Replies
Hey ACraig08,
I found a little tricky way to do this from power query, I hope I can explain myself good enough.
- Duplicate your original table
- On the original table add a new Index Column starting from 0.
- On the duplicated table add a new Index Column starting from 1.
- Now on the original table you can go to Combine tables and you use the new index as key to join them, on the original table you have your row 2 as 1, but on the duplicated one you have the row 2 as 1 so you can combine them and expand the avg ppu column moved one row.
- Just delete the New Index and you should be good to go
I hope this helps
Hi,
This calculated column formula works
Column = Data[Avg. Awarded PPU]-COALESCE(LOOKUPVALUE(Data[Avg. Awarded PPU],Data[Fiscal Year],CALCULATE(MAX(Data[Fiscal Year]),FILTER(Data,Data[ITEM_DESCRIPTION]=EARLIER(Data[ITEM_DESCRIPTION])&&Data[Fiscal Year]<EARLIER(Data[Fiscal Year]))),Data[ITEM_DESCRIPTION],Data[ITEM_DESCRIPTION]),Data[Avg. Awarded PPU])Hope this helps.
I broke it up into seperate columns to make it easier to calculate the percent change:
Previous Avg. =
COALESCE(LOOKUPVALUE(Data[Avg. Awarded PPU],Data[Fiscal Year],CALCULATE(MAX(Data[Fiscal Year]),FILTER(Data,Data[ITEM_DESCRIPTION]=EARLIER(Data[ITEM_DESCRIPTION])&&Data[Fiscal Year]<EARLIER(Data[Fiscal Year]))),Data[ITEM_DESCRIPTION],Data[ITEM_DESCRIPTION]),Data[Avg. Awarded PPU])
Change = Average - Previous Avg.
% Change = Change / Previous Avg.
Your code was helpful, thank you.
You are welcome.
