We will see how can we use Power Query (Get & Transform Data) to connect the Excel File from OneDrive and refresh it. Currently, Power Query hard code the file path in the source step when we get data from file/folder if the OneDrive is setup on your PC.
If you share this workbook with another user and when they refresh Power Query they will be prompted with an error due to location not found.
Let us look the solution to make it work
Make sure you store the file to OneDrive.
We need to extract the correct link to the source file.
Simply select the file and click download the file. Upon download, this will generate the download link which will be helpful for us to connect the data to Power Query
Now open new Excel Workbook, Select Data Ribbon > Get & Transform Data > From Web and paste the link which we have copied.
Your Excel source file in OneDrive is now linked.
Note: This link is active only 24hr as far I have tested. After that, you need to generate a new link.
When you share this Query file, anyone can able to refresh the query without any issue.
When the user enters the new data, the new records will get the update. This also allows us to clean the data and add it to the Data Model. We will add a few records to the source file and refresh this Power Query file.
Was the information helpful in this blog?
- If yes, hit that share button and show the excitement to the world.
- Subscribe to our free posts.
If no, please let us know what to improve.