Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Polly123
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
Polly123
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!

View solution in original post

3 REPLIES 3
Polly123
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!

wdx223_Daniel
Super User
Super User

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

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors