cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

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

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.

1 ACCEPTED SOLUTION
Regular Visitor

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!

3 REPLIES 3
Regular Visitor

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!

Super User

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

Regular Visitor

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors