Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Powerquery – only pull data from weeks that have fully ended

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

 

1 ACCEPTED SOLUTION
Payeras_BI
Super User
Super User

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?

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

2 REPLIES 2
Payeras_BI
Super User
Super User

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?

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain
Anonymous
Not applicable

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

best regards

Bas

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors