Dynamically connect/upload/fetch/access an excel file from OneDrive or SharePoint using Power BI (Power Query)

We all know how to connect to a file (let’s say excel.xlsx) residing in OneDrive or SharePoint, but in this post, we will go over the steps that will fetch the latest file from a designated folder.

You may ask. That why we may want to do this? Well, there are several reasons. One such use case is when your end-user uploads (dumps) an data file in a particular location at a pre-defined upload cadence, and a Power BI report must fetch that latest file.

One brute force method is always to change the connection string point to that latest file. However, you will have to reconnect or point to the right file every single time. Tedious, manual, and brute force, but this is good when you want to do unit testing.

Or if you want to do this task dynamically then please read on.

For those who want a quick explanation then here it goes

1. Connect to the desired sheet
2. Modify the connection to SharePoint.Contents (I personally find this better, here is why)
3. Create the query parameters based on your file and folder structure
4. Sort by on date modified or date accessed (your choice).
5. The above choice depends on how this sheet is used (is it modified or a new copy is uploaded every time)
6. Filter the rows to .xslx extension
7. Keep the top row
8. Filter for the hidden files
9. Invoke a custom function
10. And that’s it. You have done it

Below is my final result image.