MS Excel — Connecting PowerQuery To An Excel File In The Cloud (OneDrive) — Part 3
Microsoft’s Cloud Service Is Next Up for My Cloud Excel File Data Acquisition…
As a finance and accounting professional, I spend a lot of time working with data.
Increasingly, I also get to deal with cloud data across many various cloud storage services — primarily Box.com, Dropbox, OneDrive and GoogleDrive.
Knowing how to retrieve data directly Excel files from these cloud services comes in very handy!
In Part 1, I explored accessing Excel files in Box.com.
In Part 2, I addressed Excel files in Google Drive.
Here’s The Process for Excel Files in Microsoft OneDrive
Locate the Excel file in OneDrive.
Access the data source link for use in Power Query. To do this, right-click the file and select “Download”.
Although the file will download, what we care about is the download LINK that is generated.
Next, access the Downloads information for your Web Browser (I am using Chrome here — which is “CTRL + J”).
This is the link we use in Excel Power Query to access the data directly on the Internet.
Open a new Excel workbook. Using Excel 2016, I select “Data >> Get & Transform Data >> From Web”.
Paste the link into the Dialog box that appears. Click “OK”. If prompted, connect using “Anonymous” and click “Connect”.
Select the Excel sheet you want to import.
Select the worksheet tab that you want to Import and click Edit — do not immediately Load the query.
Finally, this is where we edit the query and shape the data set. For purposes of this discussion, I am not going to do anything other than “Load” the data.
Editing and shaping this data is for another discussion!
Load the data into a Table in Excel.
That’s it. Now we can start working with and shaping our data set. Whenever the file in OneDrive is modified, the changes will be reflected in the Excel file data set!
If you are a Microsoft OneDrive user, this capability can have quite and impact on your data access and management process.
Think about how you can take advantage of this capability…
