Forum Discussion
Power query help
Am looking to create a column in power query editor that will take the monch a order was invoiced then based in the number of months that it needs to be invoiced for will give me the dates. For example;
for an order invoiced on 23/04/2019 and the number of months being 3 the new column should give me 3 months worth of dates for example 23/05/2019 23/06/2019 23/07/2019. Below is a screenshot of the data i am trying to create this with. Any help will be gratefully appreciated
Example table
Calculated column
Expand to new rows
Expanded tableNew calculated columnResult
5 Replies
https://docs.microsoft.com/en-us/powerquery-m/list-dates
Create a new column with your month column as count and month as duration. Then expand into new rows.tex628 thank you so much for the response so would it look like this
List.Dates([DateInvoiced],[Months], [Months] )
thank you
Nevermind that, apparently its not that simple.
It appear that list.dates cant use month as increment which leaves us with a more complicated solution.
Use List.Numbers to create a record for each month. So you would do something along the lines of:List.Numbers( 0 , [Month] )
https://docs.microsoft.com/en-us/powerquery-m/list-numbers
When the list is expanded you should end up with 1 record representing each month. 1, 2, 3, 4, 5 etc.
Now you want to create a new column using:Date.AddMonths( [DateInvoiced] , [New column that you just made] )
https://docs.microsoft.com/en-us/powerquery-m/date-addmonths
This should give you the desired result! Sorry for the missdirect in the beginning, hope it works out.
Br,
J