cancel
Showing results 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

Regular Visitor

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

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.

1 ACCEPTED SOLUTION
Memorable Member

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

3 REPLIES 3
Memorable Member

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

Regular Visitor

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.

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

Memorable Member

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.