Power Query: Shift results over in a linked table by "n" months

[Polly123]

Hello All,

New to Power Query. I have set up various very useful duplicating tables with formula that appear to work (!) but I am now stuck on the point below:

I have Table 1 showing the amounts expected to be invoiced to customers.

Table 1
Customer	Months to pay	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23
Customer A	2	6000	4000	7000	3000
Customer B	1	2000	1000	3000	4000

In Table 2 which is linked (first 2 columns only) I would like to show the same amounts but shifted right depending on how many months they usually take to pay ie. so I can see when the cash will be received.

Table 2
Customer	Months to pay	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23
Customer A	2			6000	4000	7000	3000
Customer B	1		2000	1000	3000	4000

1. in Table 2 for Customer A, December 22, I would like the formula to be something like

=Table 1 [@[[Dec-22]-Months to Pay]]

so that it picks up the amount from Oct 22 on Table 1.

What would be the best formula to use? 

Thank you for any guidance you can give.

[Polly123 · Accepted solution]

In case it is of use to anyone else, I just ended up creating a normal Excel spreadsheet and used the INDEX MATCH functions to shift the receipts over by the right about of months. I had to use the EDATE function to get the date into the correct format to deduct a number of months from it so that I could then Index/Match it.

Hope that helps!

[wdx223_Daniel]

=#table(Table.ColumnNames(Table1),Table.ToList(Table1,each List.FirstN(List.InsertRange(_,2,List.Repeat({null},_{1})),List.Count(_))))

[Polly123]

Wow! Thank you Daniel. I had no idea it would be so complicated. I tried to break your formula down to understand each part but it looks like I have a lot to learn.

At first it got stuck on the “null” part. When I retyped it, it got stuck on #table, so I am obviously doing something wrong.

I need to actually understand what this is doing first before I use it but you have given me a great place to start.  I will go through it slowly and work it out.

Thanks for your help.