Forum Discussion
Power Query - Most Recent File within a week
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.