Working on data from the latest file in a folder is a very common requirement. I have been asked about it in one of the Company Interviews. Well, at that moment of time I was having no idea about it but later I got this as a requirement in one of my project requirements.
So, How do we get data from the latest file in a folder in Power Query? Check the below steps and you can practice with me in your machine. Create a dummy folder with various excel files and follow the steps which I have mentioned below.
1. In Microsoft PowerBI Desktop, Click on Get Data >> Folder and click on the connect button at the bottom.
2. Input the folder path from where data from the latest file need to be brought in Power BI and click on the OK button.
3. In the pop-up windows, click on the Transform button and then Power Query will get opened.
4. In the Power Query, Sort the date column by clicking the down arrow at the right of the column name. Then sort the column in descending order.
5. Click on Reduce Rows Button and select Keep Top Rows and then Enter Keeps Rows as 1.
It will now show only the row having the date.
6. Click on the double symbol on the right of the Content Column.
7. Select the sheet having Data and click on the Ok button.
8. Now, You have data ready. It is optional to remove the Source. Name Column which shows the file name.
9. Click on Close and Apply button and get out of Power Query.
It was an easy task to get the data from the latest file from a folder. This scenario is applicable to servers where the middleware messaging solution like MQ has been implemented and lot of files use to come to a folder and we have a requirement to analyze the data of latest file.
I hope that the article was helpful to you. Please comment and let me know your feedback.