Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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?
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?
@Payeras_BI that is a brilliant and simple solution, works perfect! Thank you for the quick reply!
best regards
Bas
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
50 | |
19 | |
12 | |
11 |