Powerquery – only pull data from weeks that have fully ended

[Anonymous]

Hi all,

I’m importing a table with sales data using powerquery. That sales data has a delay of roughly two weeks, and it’s updated and coming in every day. It looks somewhat like this:

Date	Sales
1-1-2021	100
1-2-2021	100
1-3-2021	100
1-4-2021	100
1-5-2021	100

As it’s a lot of data and I only need to have it by week, I’m using the group by function in Powerquery to group these dates into weeks:

Date week	Sales
1-3-2021	700
1-10-2021	700
1-17-2021	700
1-24-2021	700
1-31-2021	400

This model runs every day. The problem I’m facing is that the last week might only cover a couple of days. I would like to know how I can add a column in Powerquery that shows me whether the week was finished, so that I can filter out all the sales from the week that has not yet finished:

Date week	Sales	Week has finished
1-3-2021	700	Yes
1-10-2021	700	Yes
1-17-2021	700	Yes
1-24-2021	700	Yes
1-31-2021	400	NO

How can I create such a column / formula in powerquery?

best regards

Bas

[Payeras_BI · Accepted solution]

Hi @Anonymous ,

How about when Grouping By weeks add a Distint Count of Date column and then rule out the ones not being equal to 7?

[Anonymous]

@Payeras_BI  that is a brilliant and simple solution, works perfect! Thank you for the quick reply!

best regards

Bas