Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kimmend
Frequent Visitor

Weekly updated report, how to use the latest entry for the month

I have a set of data that is updated weekly, stored in the same folder in Sharepoint. 

 

I would like to use the latest version of the imported data to represent that month's values. I.e. if the latest update in January is 28.1.2018, that would be recognized as the only data for January and other records in January would be discarded. If then the latest update for February is 15.2.2018, that would be represented for February, until new entries in February is uploaded. 

 

How can I filter out the months' earlier records and only include the latest records per month? I am using the Sharepoint folder retreival, and combining the files from there. I tried to use the Group by function but am not able to get the filter to work.

 

I would like to do this already in the query editor to limit the dataset.

1 ACCEPTED SOLUTION
kcantor
Community Champion
Community Champion

@kimmend

In query editor you can use Table.Buffer to remove duplicates after you sort to maintain a sort. I have done this to filter by date when multiple entries for the same item exists. I just sort descending by date, table.buffer, then remove duplicates. Here is the source of how to do this. Unless someone has a more elegant solution, this is what I use.

https://social.technet.microsoft.com/Forums/en-US/00a09332-fe6e-47a4-a8a9-b71e38b01a86/how-to-remove...

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Xandman
Helper II
Helper II

Hi

 

I'm fairly new to Power BI, I couldnt follow the solution.

 

I have the same scenario, a folder is updated weekly and I need to get the lastest file for each month.

 

I found a possible solution on youtube:https://www.youtube.com/watch?v=HBFc1eBSfb8&t=479s 

 

But, I can't get it to work. 

 

still shows the multiple filesstill shows the multiple files

new measurenew measure

 

Effective date is a column inside the spreadsheet that indicates the date a file was generated. 

kcantor
Community Champion
Community Champion

@kimmend

In query editor you can use Table.Buffer to remove duplicates after you sort to maintain a sort. I have done this to filter by date when multiple entries for the same item exists. I just sort descending by date, table.buffer, then remove duplicates. Here is the source of how to do this. Unless someone has a more elegant solution, this is what I use.

https://social.technet.microsoft.com/Forums/en-US/00a09332-fe6e-47a4-a8a9-b71e38b01a86/how-to-remove...

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks for the tip @kcantor. It seems to work quite well. As i understand it, i need to add a Month&Year column in order to group according to that column. Then it will exclude the smallest values in the date column for those month groups.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.