Fill down dates (weekend) - when there's no row to fill?

[grggmrtn]

Our data looks something like this:

PersonID	Date		Pause

1		03-01-2019	0
1		04-01-2019	1
1		07-01-2019	1
1		08-01-2019	1
2		17-01-2016	1
2		18-01-2016	0
2		21-01-2016	0
2		22-01-2016	0
2		23-01-2016	0

So basically, is a person "paused" on which days.

But as you can see here, my table doesn't include dates for weekends (or holidays, I've just noticed). Unfortunately, I need to have the weekends because of some other stuff that's happening in my report.

The pause boolean continues during the weekend with the value for Friday.

If there was a blank row where the weekend SHOULD be, I'd just do a fill down on the pause, and be happy. But there's no blank row.

To summerise, I need the dates for each person to be consecutive, and to include the missing weekend dates with Friday's value for Saturday and Sunday.

Any ideas?

[HotChilli · Accepted solution]

Could you :

create a Date table in Power Query (create a blank query-> use List.Dates)

This would get you a Date table with one column.

Merge the Date table with your existing table (left join so you keep all the rows in the date table)

Then you can use the 'Fill Down' idea that you proposed.

[grggmrtn]

Of course! Thanks @HotChilli 

[HotChilli]

Did you get this sorted? I think you have deleted a message that you posted in the thread?

[grggmrtn]

Hey @HotChilli thanks for responding.

Yeah I posted a follow up because the missing weekends were giving me problems, which I deleted after speaking with the customer.

I discovered that the weekend dates were THERE, but a column that I wasn't showing here (because I didn't think it was needed for the example) was only set up to show weekdays - meaning that there was no data for the weekends anyway. And PowerBI was automatically filtering the weekend dates away in the query.

I'm not sure that I found much of an answer to getting the weekends back, other than that fact that the customer decided that they didn't need data for the weekends anyway.

So instead of focusing on specific dates, I created visuals for the customer that slice data by week number, and the customer seems happy (for now 🙂 )