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

Question

Hello Power Query Community!&nbsp; 🙂&nbsp;Below is a graphic of my current Power Query challenge in Excel:&nbsp;In my current "Before" table, I have a list of 4 Effective Dates (listed as the first day of the month) and respective Rates.&nbsp;In my desired "After" table, however, I need to use Power Query to:&nbsp; a) Insert a row for each month between the Effective Dates in my current table and through the current month (June 2024)&nbsp; b) Drop the respective rates accordingly to the above, newly-inserted rows through the current month&nbsp;&nbsp;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.&nbsp; (or, should that be, "happily ever after" table?)&nbsp;Any help/guidance/suggestions you could provide me would be GREATLY appreciated... and I THANK You in advance.&nbsp;Respectfully, CincyKJ&nbsp;&nbsp;

anonymous · Accepted Answer

Hi&nbsp;CincyKJ&nbsp;,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 TaoIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

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

2 Replies

Related content

Identity Insert On Warehouse table

insert custom shape

Power BI – YoY Measure Ignoring Product Filter but Respecting Slicers

Inserting Subtotals in a Table

Insert a Projectwise link