Forum Discussion
Power Query - Most Recent File within a week
Hi, admincaleb007
This problem is easy to solve in Desktop. Add year and week columns in the date table.
Sample data:
Measure =
IF (SELECTEDVALUE ( 'Table'[Date modified] )
= CALCULATE (MAX ( 'Table'[Date modified] ),
ALLEXCEPT ( 'Date', 'Date'[Weeknum], 'Date'[Year] )),
1,
0
)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi!
Thank you for the reply.
However, when looking at your data, I see an issue with my explanation.
I have 40 groups. Each group has 2 separate categories (inside, outside). Regardless of category, all the files are created on the same day 99% of the time. However, there are times when there is an error, and the files are ran a second time successfully.
Below is an example filtered down to the 2 most recent errors. Row 3, Group A with Category inside but more recently created 11/24/2022 would be used. Row 4, Group A with Category Outside has no errors, and is the most recent result for that week, so that data is used. I used green color to highlight some of the examples, and which files would be used.
| Group Names | Category | Date Created | Weeknum | Year |
| A | inside | 11/23/2022 0:00 | 48 | 2022 |
| A | inside | 11/24/2022 0:00 | 48 | 2022 |
| A | outside | 11/23/2022 0:00 | 48 | 2022 |
| B | inside | 11/23/2022 0:00 | 48 | 2022 |
| B | inside | 11/24/2022 0:00 | 48 | 2022 |
| B | outside | 11/23/2022 0:00 | 48 | 2022 |
| B | outside | 11/24/2022 0:00 | 48 | 2022 |
| C | inside | 11/23/2022 0:00 | 48 | 2022 |
| C | inside | 11/24/2022 0:00 | 48 | 2022 |
| C | outside | 11/23/2022 0:00 | 48 | 2022 |
| D | inside | 11/23/2022 0:00 | 48 | 2022 |
| D | inside | 11/24/2022 0:00 | 48 | 2022 |
| D | outside | 11/23/2022 0:00 | 48 | 2022 |
| E | inside | 11/23/2022 0:00 | 48 | 2022 |
| E | inside | 11/24/2022 0:00 | 48 | 2022 |
| E | outside | 11/23/2022 0:00 | 48 | 2022 |
| E | outside | 11/24/2022 0:00 | 48 | 2022 |
| F | inside | 11/23/2022 0:00 | 48 | 2022 |
| F | inside | 11/24/2022 0:00 | 48 | 2022 |
| F | outside | 11/23/2022 0:00 | 48 | 2022 |
| F | outside | 11/24/2022 0:00 | 48 | 2022 |
| G | inside | 11/23/2022 0:00 | 48 | 2022 |
| G | inside | 11/24/2022 0:00 | 48 | 2022 |
| G | outside | 11/23/2022 0:00 | 48 | 2022 |
I decided to create a new column called "merged" with power query, which combines the "group name", "category", "year", "week of year".
For example:
A,inside,2022,48
A,inside,2022,48
A,outside,2022,48
B,inside,2022,48
B,outside,2022,48
B,outside,2022,48
This enables me to quickly see which items have duplicate files on the same weeks. I then created an "Age" column that shows the amount of days after the file was created.
Merged Age A,inside,2022,48 6.00:00:00 A,inside,2022,48 7.00:00:00 A,outside,2022,48 7.00:00:00 B,inside,2022,48 7.00:00:00 B,outside,2022,48 6.00:00:00 B,outside,2022,48 7.00:00:00 With the previous table additions, I believe I can use the combination of Age and Merged columns to filter out the older erroneous data. Is that possible? Is that a measure, or some other query? I'm not sure how to actually use this data to use the most recent file for a given category, week, year, team.
Hi v-zhangti
I am trying to understand the measure you wrote, but recreate it for what I have built.
MostRecent = IF(selectedvalue('Folder Paths'[Merged])= CALCULATE(MIN('Folder Paths'[Age])),1,0)The above measure is not going to work, but I believe with some additional understanding and help, it would create the same filter as you previously showed.MostRecent = CALCULATE(MIN('Folder Paths'[Age]),FILTER('Folder Paths','Folder Paths'[Merged]))(error : Cannot Convert the values under merged of type text to type True/False)Are you able to assist? I believe my most recent reply makes the problem easier to resolve:When there is a duplicate value under "Merged", then use the row value where "Age" is the lowest.