How to Retrieve Data from SharePoint and OneDrive

TL;DR

Retrieve data from SharePoint and OneDrive using Power Query requires specific steps. For individual files, obtain the file path through the desktop Excel app. Use the SharePoint folder connector for folders and shared libraries, ensuring accurate filtering and proper permissions for data access and refreshing.

Transcript

nowadays many of us are saving our files to the cloud using one drive business or SharePoint online unfortunately getting data from these sites using power query is not so straightforward so in this video I'm going to step you through the three scenarios getting data from an individual file on one drive or SharePoint getting data from a SharePoint ... Read More

Key Insights

• Accessing data from OneDrive or SharePoint using Power Query requires specific steps depending on the file location, such as individual files, folders, or shared libraries.
• For individual files, the correct file path must be obtained by opening the file in the desktop Excel app and copying the path from the file info section.
• Connecting to a SharePoint folder involves copying the site URL up to 'layouts' and using the SharePoint folder connector in Excel to filter and access the needed files.
• To access data from SharePoint shared libraries, users need to adjust the URL by removing certain prefixes and use the SharePoint folder connector in Excel.
• Power Query is case-sensitive when filtering paths, so exact path names must be used to ensure accurate data retrieval from SharePoint folders.
• Sharing files with others requires setting appropriate permissions in OneDrive or SharePoint, allowing specified users to access and refresh the data.
• Users must log in with their organizational account when accessing data through Power Query, and can manage credentials through data source settings.
• The SharePoint folder connector is only available in Excel 2019 Professional Plus and Microsoft 365 Apps for Enterprise, limiting access to certain users.

Questions & Answers

Q: How do you obtain the correct file path for a OneDrive file?

To obtain the correct file path for a OneDrive file, open the file in the desktop Excel app, go to the file tab, and select 'Info'. From there, you can copy the path, which ensures it is suitable for sharing and use across different devices. This path is different from the one obtained by right-clicking the file in the browser.

Q: What is the process to connect to a SharePoint folder using Power Query?

To connect to a SharePoint folder using Power Query, copy the site URL up to 'layouts', and use the SharePoint folder connector in Excel. After pasting the URL, authenticate with your organizational account. You can then filter and transform the data to access only the necessary files and folders, which can be combined if needed.

Q: How can you share files with others using OneDrive or SharePoint?

To share files with others using OneDrive or SharePoint, set appropriate permissions by right-clicking the file or folder and selecting 'Share'. You can specify who can access the files by entering their names. Ensure that the shared users have the necessary permissions to open the Excel file and refresh the Power Query data.

Q: What are the limitations of using the SharePoint folder connector?

The SharePoint folder connector is only available in Excel 2019 Professional Plus and Microsoft 365 Apps for Enterprise. This limitation means that users with other versions of Excel may not have access to this feature, restricting their ability to connect to and manage data from SharePoint folders directly.

Q: How does Power Query handle case sensitivity in SharePoint paths?

Power Query is case-sensitive when filtering paths in SharePoint. This means that users must ensure they type the path exactly as it appears in SharePoint, including correct capitalization, to accurately filter and access the desired data. Failure to do so may result in incorrect or incomplete data retrieval.

Q: What steps are involved in accessing data from a SharePoint shared library?

To access data from a SharePoint shared library, adjust the URL by removing certain prefixes, such as 'my-', and use the SharePoint folder connector in Excel. Authenticate with your organizational account, then filter and transform the data to access and combine files as needed, similar to the process for SharePoint folders.

Q: How can users manage their credentials when using Power Query with SharePoint?

Users can manage their credentials by accessing the data source settings in the query editor. They can edit permissions, change the privacy level, and sign in with their Microsoft account. If sharing the file, other users will need to set their log-on credentials to access and refresh the data correctly.

Q: What should be done if new files are added to the SharePoint folder?

If new files are added to the SharePoint folder, refresh the Power Query to update the data. The query is connected to the SharePoint folder, so any additions or changes will be reflected when the query is refreshed, allowing for continuous data updates and consolidation from the folder.

Summary & Key Takeaways

• The video provides a detailed guide on using Power Query to access data from OneDrive and SharePoint, covering individual files, folders, and shared libraries. It emphasizes the importance of obtaining the correct file path and using the appropriate connectors in Excel.

• For individual files, the video explains how to obtain the correct file path by opening the file in the desktop Excel app and using the file info section. This ensures the path is suitable for sharing and use across different devices.

• Accessing SharePoint folders and shared libraries involves using the SharePoint folder connector in Excel, with specific instructions on filtering paths and managing permissions to allow data sharing and refreshing by other users.