Can you enter a formula in a column in Power Query Editor?

[Anonymous]

I want to enter the following formula for each column(after Savings Plan Cost) in my dataset:

=IF(Savings Plan Cost < [columnheader], (Savings Plan Cost/0.6)-Savings Plan Cost, ([columnheader]/0.6)-[columnheader])

So for example when my column header is 248, the formula should be:

==IF(Savings Plan Cost < [248], (Savings Plan Cost/0.6)-Savings Plan Cost, ([248]/0.6)-[248])

I have 999+ such columns, how do I change the the values in each column to the formula above.

Attached is a screenshot of my dataset with the first 4 columns.

[jthomson]

This has surely, surely got to be done more efficiently by unpivoting your extreme number of columns and just making one formula based on the value in the column header...

[Anonymous]

Thanks - how so?

I pivoted the columns because I need to see how the values in the Savings Plan Cost column relate to each other through the formula - so I basically have to compare the column header value to each of the SP Cost values. Any suggestion on improvement of the process is also much appreciated!

[PhilipTreacy]

Hi @Anonymous 

Any chance of getting some sample data so I don't have to type 1000 columns worth of data.

Thanks

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.