Forum Discussion
Power Query - Most Recent File within a week
Hi,
I was looking at some of my data and noticed a large anomaly. Looking into it, it seems that there were 2 different files created on 2 different days, due to an error. Because of this, powerbi is reading both files because they are from the same week, and aggregating them together.
I'm trying to figure out how to only use the most recent file from within the same week.
So for this example, it would be Week 48 has 2 files, so filter out the older one (Nov 23 2022) keeping the new one. This would also need to be able to interpret the year, as there is data for Week 48 for many years prior as well.
4 Replies
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.
