How to generate a new table by duplicating items from another table based on recurrence

[EvaPBI]

Hi, 

To facilitate data entry, I would like to generate a new table (Table 2) by duplicating items from another table (Table 1) based on the recurrence.

Table 1 (Input - including recurrences)

Table 2 (Output - any perpetual recurrence (bi-monthly, weekly) ends at the end of the last period)

For the 'Period' recurrence,
I also have a Period Reference Table that contains period information (e.g. Start Date, End Date).
The due date is always the End Date column from the Period Reference Table.
That's why Task 2 generates 3 rows in Table 2.

Period Reference Table

Any idea where to start?

Thank you 🙂
-Eva T.

[jbwtp]

Hi @EvaPBI,

the overall idea is to split the table by the way it is processed and then combine it back. This is how it is done for the Punctual and Period:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VczBCsIwDIDhVyk5R9qmw50dehfcbfQQZ6HDbYVtFXx7m13U2x/yJV0HLa9PZRUg3HjhKNEMhynNWxzfZSCnDWky5MBj4Sn3Ud15DSLbwJM6SV3z3G+Zx5K1NtXPgfynHTfnXYZlSI8SFiusv8bJ8vLif+LwiJbA+w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Title = _t, Owner = _t, Recurrence = _t, #"Start date" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Title", type text}, {"Owner", type text}, {"Recurrence", type text}, {"Start date", type text}}),
    #"Punctual ->" = Table.RenameColumns(Table.SelectRows(#"Changed Type", each ([#"Recurrence"] = "Punctual")),{{"Start date", "Due date"}}),
    #"Prtiod ->" = Table.ExpandListColumn(Table.TransformColumns(Table.SelectRows(#"Changed Type", each ([#"Recurrence"] = "Period")), {{"Start date", each Text.Split(_, ","), type list}}), "Start date"),
        #"-- Format" = Table.TransformColumnTypes(#"Prtiod ->",{{"Start date", Int64.Type}}),
        #"-- Merged Queries" = Table.ExpandTableColumn(Table.NestedJoin(#"-- Format", {"Start date"}, Periods, {"Period"}, "Periods", JoinKind.LeftOuter), "Periods", {"End date"}, {"Due date"}),
        #"-- Clean up" = Table.RemoveColumns(#"-- Merged Queries",{"Start date"}),
    Combine = Table.Combine({#"Punctual ->", #"-- Clean up"})
in
    Combine

I could not figure out the logic behind the periodic (i.e. bi-monthly, weekly), but if you can explain it I think I should be able to help with the coding. Otherwise, you may find it quicker t odo it yourself and then just add to the code above.

Cheers,

John

[EvaPBI]

Hi John!

Thank you for your help! I was able to replicate your solution and it works fine!
About the last category, I tried to add the weekly logic in the query, but it still doesn't work.

I previously made a mistake about this type of frequency. There are actually two types: weekly or every two weeks (2weeks).
For weekly, I would like to generate 52 rows (from the Start Date + 7 days for each week)
For 2weeks, I would like to generate 26 rows (from the Start Date + 14 days)

I saw a similar solution on the forum, but I couldn't adjust my query.

Thank you very much,

-Eva

[jbwtp · Accepted solution]

Hi @EvaPBI,

This is the function which generates a list of dates between the start and end date with a given step:

f = (Start, End, Step)=> List.Generate(()=>Start,  each _ < End, each Date.AddDays (_, Step) )

You can use it as this:

f(#date(2023, 1, 1), #date(2024, 11, 30), 14)

Cheers,

John