Need to Insert Rows for Months between two respective Months listed in the same Date column

[CincyKJ]

Hello Power Query Community!  🙂

Below is a graphic of my current Power Query challenge in Excel:

In my current "Before" table, I have a list of 4 Effective Dates (listed as the first day of the month) and respective Rates.

In my desired "After" table, however, I need to use Power Query to:

  a) Insert a row for each month between the Effective Dates in my current table and through the current month (June 2024)

  b) Drop the respective rates accordingly to the above, newly-inserted rows through the current month

The table of historical rate data I need to apply the above challenge to is fairly huge (56K rows) and I'm seeking the most efficient means possible to create my "after" table.  (or, should that be, "happily ever after" table?)

Any help/guidance/suggestions you could provide me would be GREATLY appreciated... and I THANK You in advance.

Respectfully, CincyKJ

[Anonymous · Accepted solution]

Hi @CincyKJ ,

I suggest you add a calendar table like this one:

In the Power Query, choose the Before Table, and select Merge Queries:

Choose column Rate and choose fill down:

And the final output is as below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJW0lEyVYrViVYyQQiYgQUMDRAi5hARiIAJUMBCKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Effective Date" = _t, Rate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Effective Date", type date}, {"Rate", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Effective Date"}, Calendar, {"Date"}, "Calendar", JoinKind.FullOuter),
    #"Expanded Calendar" = Table.ExpandTableColumn(#"Merged Queries", "Calendar", {"Date"}, {"Date"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Calendar",{{"Date", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Rate"})
in
    #"Filled Down"

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @CincyKJ ,

I suggest you add a calendar table like this one:

In the Power Query, choose the Before Table, and select Merge Queries:

Choose column Rate and choose fill down:

And the final output is as below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJW0lEyVYrViVYyQQiYgQUMDRAi5hARiIAJUMBCKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Effective Date" = _t, Rate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Effective Date", type date}, {"Rate", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Effective Date"}, Calendar, {"Date"}, "Calendar", JoinKind.FullOuter),
    #"Expanded Calendar" = Table.ExpandTableColumn(#"Merged Queries", "Calendar", {"Date"}, {"Date"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Calendar",{{"Date", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Rate"})
in
    #"Filled Down"

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CincyKJ · Accepted solution]

Hi Dino Tao.

Thank you ever so much.  "Merging Calendars" worked perfectly for me!