Forum Discussion
Solved
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 Quer...
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.
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