Power Query (Last Two Columns Reference - Hardcoding Problem)

[Fugdkn]

Hey. So basically I want to do something where the last two columns of a table in power query will have their average taken. This is what it currently looks like:
https://gyazo.com/aa664bf02d6b17f65761ee745428b163

Does anyone know how I can stop the hardcoding of the Week 38 and Week 39, to instead just reference the last two columns of the previous step? Thanks.

I have attached a sample file if it's of any use.

Oh, I also have an issue with reordering the columns too because the advanced editor hardcodes the order, so when new weeks gets added, the order get's messed up unless you go back and adjust (not the end of the world, but a bit annoying, and it would be nice to avoid this issue). Thanks.

[Fugdkn]

Thought I would at least give it a go myself even if I have no idea what I am doing with m code. I just tried:

Please Login or Register  to view this content.

But it says
"Expression.SyntaxError: Token RightParen expected."

Any thoughts on how I can fix this? I don't really get why it's causing problems.

[JeteMc]

This is more of a work around than a solution, as the averaging is done on the Daily Sales sheet rather than in Power Query.
Column G calculates the average of the last two weeks using: =AVERAGE(SUMIFS(H7:DL7,H$1:DL$1,AGGREGATE(14,6,H$1:DL$1/(H7:DL7<>""),1)-1),SUMIFS(H7:DL7,H$1:DL$1,AGGREGATE(14,6,H$1:DL$1/(H7:DL7<>""),1)))
Note that for this to work the week numbers in row 1 are converted to numbers then custom formatted: "Week" 0
I feel that from this point you will be able to use Power Query to produce the table on the Weekly Sales sheet the way you would like it.
Let us know if you have any questions.