Forum Discussion
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.
3 Replies
jbwtpMemorable 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 CombineI 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
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
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
