I have a tough one at hand…
The Bills export I get from our system does not generate the recurring ones, it just provides a info column with the Frequency type.
When an expense is recurring, i.e. every other month from the Transaction Date (lets suppose Oct 3rd 2021), I need to generate (at least for the current year) the remaining transactions - in this example one more on Dec 3rd 2021. I wouldn’t mind though if I could generate the next n transactions.
I need to do that for all different Frequency types, apart from ‘Only once’.
Can this be managed in Power Query? I am open to all suggestions
I can recommend a PQ course if you want to get up to speed, you’ll find it in the portal…
I did some prepping. First created this supporting query (Important! know that PQ is case sensitive so these Freq names need to match with the Freq names in your ScheduledBIlls table).
Next you can optionally set an upper limit for creating new dates, should you omit that the last date in your date table will be the upper limit. However if you do set a date you can’t exceed beyond the last date in your date table.
and finally I created this custom function: fxCreateDates
( StartDate as date, myFrequency as text, optional endDate as any ) as list =>
let
FreqList = List.Buffer(tInterval[Frequency]),
IntervalList = List.Buffer(tInterval[Interval]),
Interval = IntervalList{List.PositionOf(FreqList, myFrequency)}?,
maxDate = if endDate <> Date.Type or endDate = null then Date.From(CalenderMax) else List.Min( { Date.From(CalenderMax), Date.From(endDate) }),
Source = List.Generate(
() => [x = 0, y = maxDate, z = StartDate],
each [z] <= [y] and not List.IsEmpty( List.RemoveNulls( {Interval} )),
each [x = [x] + 1, y = [y], z = Date.AddMonths( [z], Interval )]
),
CreateTable = Table.FromRecords(Source)
in
try CreateTable[z] otherwise {StartDate}
.
Invoked that on your ScheduledBIlls table and expanded the lists to new rows.
@Melissa
Your custom function works like a charm, I couldn’t make it without your help.
Thank you.
PS. Tried to understand the function but my intermediate BI level is not enough