How To Upload Excel To Onedrive

While Microsoft makes it real piece of cake to get information into Ability Query or Power BI from a file on your local desktop (Get Data > Excel workbook) they don't do a peachy job of explaining how to get that file if it's stored on OneDrive for Business or SharePoint. This article is meant to demystify the process!

Quick Links

At that place are two parts to this article, getting a single file or getting multiple files and combining them (a slap-up use instance would be consolidating monthly sales data where each file had a particular calendar month). Use the links below to get correct to your use case.

Getting Data from a Unmarried File on SharePoint or OneDrive
- Get a link to your Excel file from OneDrive/SharePoint directory
- Get link to your Excel file from with Excel
Getting Information from Folders (multiple files)
- Getting Data from a SharePoint Folder (multiple files)
- Getting Data from a OneDrive Folder (multiple files)

Getting Information from a Single File on SharePoint or OneDrive

At that place are two ways to get a link to the file from SharePoint or OneDrive, either from your directory or from with the Excel file.

Get a link directly from SharePoint or OneDrive

Click the ellipsis button side by side to the file y'all want and then select Details (do not use copy link, it will not piece of work).
In the details pane, scroll all the way to the bottom and select the re-create icon next to the path push.

Go link directly from your Excel file

From Excel, navigate to File > Info and click the 'Copy path' button.

Importing your file into Power Query or Power BI

Now what you have your link it'south fourth dimension to bring in your data. The process is the same for Ability Query in Excel and Power BI.

Use Get Information from the Excel file or Power BI file you want to bring the data into. Note, practise non choose Excel Workbook (it won't work).

Choose Become Information > Spider web

Paste in the URL for the file that was captured from the File > Get Info step.

If you used the copy path pick to get a direct link to the file from OneDrive/SharePoint you can use the URL every bit is.
If you copied your URL from directly from Excel so here is the play a trick on - yous must delete the ?web=ane from the finish of your URL.

Next, yous'll need to sign in. Cull Organization Account and the select 'Sign in' and enter your Microsoft 365 credentials. In one case entered, click Connect (yous're about done).

On this final screen you lot'll see all of the tables and worksheets inside your workbook. As a full general dominion yous should import tables, not worksheets. Selecting tables will ensure that your information has headers. Once you lot've selected your table(s) click 'Transform Information' and become to work!

Getting Data from a Folder (multiple files)

A typical reason for getting data from a binder is to combine information. For case, you may accept a carve up Excel workbook for each month with general ledger or sales data. This might exist due to file size constraints or mayhap yous're just super organized!

Getting data from a folder is a scrap different than getting data from a single file, and it fifty-fifty varies based on where information technology is, OneDrive or SharePoint. Don't worry though, we've got you covered.

Getting Data from a SharePoint Folder

Microsoft makes getting files from folders a bit easier with their SharePoint Connector. The first thing you'll need to do is to get the URL of your SharePoint Library. Annotation this will be something like https://yourdomain.sharepoint.com/sites/yourlibrary.

Now, from Ability BI (or Ability Query in Excel) select Go information and choose SharePoint folder and then click Connect.

Next, paste the URL captured in the beginning step and click OK. The yellow highlight below is from the (i) icon.

Now you might demand to expect a minute. Power Query is now getting every file and folder in your site. We're going to utilize a text filter to filter to the folder that holds the sales data. To make your job easier move the folder path cavalcade over to the left and aggrandize it equally far as you tin can.

The next step is to detect the folder that contains the files y'all desire to combine. We'll practise this using a text filter. Below y'all can see that my folder contains "Sales Orders".

So let's add a text filter by selecting the drop down at the top correct of the folder path and then choosing text filter. Side by side, under 'keep rows' choose contains and so enter 'Sales Orders' as the criteria. and press OK.

Now you lot should see a list of simply the files you want to combine (yay for making it this far). Double click on the double arrows at the top of the Content column to go the information from the files in your desired folder.

Finally, cull the tabular array/sheet that you want and when yous click OK Ability Query will grab that same table/sheet from each file in the folder!

Getting Data from a OneDrive Binder

The process for getting files from a OneDrive folder is the same as getting it from a SharePoint folder with the exception of the URL.

Navigate to your OneDrive account in your browser and then to the folder y'all desire to import and copy the URL up to (but not including) _layouts.

Now that you have the URL the steps are identical to that of getting data from a SharePoint binder, including Get Information > From SharePoint folder (yup, even when y'all're using OneDrive).

Put information technology all together with CloudExtend Excel Analytics for NetSuite

Now that you know how to become files and folders into Power Query and Power BI it's time to beginning analyzing your NetSuite data. Get started by streaming your saved searches into Excel which, as demonstrated, is a slap-up data source for Power BI or Power Query.

Source: https://support.cloudextend.io/en/articles/5473095-get-excel-data-from-a-single-file-or-entire-folder-on-sharepoint-or-onedrive-for-business-into-power-query-or-power-bi

Posted by: coopergeoll1936.blogspot.com