How to generate queries in Query Editor with M Code

Power Query
4

Hi @deltaselect,

Give this a go, just paste it in a new blank query in your PBIX file.

let
    Source = Invoiced,
    CreateDates = Table.AddColumn(Source, "Invoice date", each let myDate = [Date] in if ([Months]) >1 then List.Transform( {0..([Months]-1)}, each try Date.AddMonths( myDate, _ ) otherwise Date.EndOfMonth( Date.AddMonths( #date( Date.Year(myDate), Date.Month(myDate), 1), _ ))) else {[Date]} ),
    ExpandDates = Table.ExpandListColumn(CreateDates, "Invoice date"),
    AddValue = Table.AddColumn(ExpandDates, "Invoice value", each [Invoiced] / [Months])
in
    AddValue

.
First create an item for each Month using the list initializer { }

image
image1160×587 48 KB

.
Built on this logic

  • First get the “Date” from the current record.
  • Test if there are rows to add
  • Transform the list items into a date, not all months have the same number of days so if that returns an invalid date, calculate the last day of the month instead.
  • If there are no rows to add, get the Date as a list

image
image1345×583 48.1 KB

I hope this is helpful.

2 Likes