Power Query - Most Recent File within a week

[admincaleb007]

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.

[v-zhangti]

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

[admincaleb007]

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

[admincaleb007]

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.

[admincaleb007]

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.