Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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.
Proud to be a Super User!
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.
Effective date is a column inside the spreadsheet that indicates the date a file was generated.
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.
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.