Filling in in-between dates when unpivoting

[Chad Bezos]

I have a table that looks like this
Name From To
a 31.01.2025 30.09.2025

If I want thise dates in the same column, I have to ununpivot the From and To columns, and so now it looks like this

Name Value
a 31.01.2025
a 30.09.2025

This is great, but it doesn’t add dates between thise two dates, which I need.

[administrator@dailyexcel.net]

You will not be able to accomplish what you want by unpivoting columns. However, you can:

Go to your Date table, add Custom Index Column, Starting Index 0, Increment 0

Go back to your Query, add Custom Index Column, Starting Index 0, Increment 0

Merge Date table into your Query using those two new columns; use Left Outer join

After expanding, you will have all of the rows from your Date table available.

Add Conditional Column, “Custom”, each if ([Joined]›=[From] and [Joined] ‹= [To]) then “keep” else “delete”

Filter Custom and keep only “keep”

Delete all the unnecessary columns.

Br,

[Chad Bezos]

I’ve followed these instructions, but now I have all of the dates in my query, not just the end-of mont dates I need.

[administrator@dailyexcel.net]

Hm, you could duplicate your Date table, filter out dates that are not end-of-month dates, and then join that table.

Br,

[Author]

Posts