Consolidate and Hyperlink to Excel files on SharePoint
Wyn Hopkins
Leading a team that solves data challenges for companies and people Power BI | Excel | Fabric | Solver Budgeting & Consolidation. Control costs, reduce risk and assist growth Microsoft MVP ??5 Million+ YouTube views
In this video, we're diving into the world of Excel and Power BI, focusing on creating hyperlinks for easy referencing when consolidating data from Excel files in SharePoint folders. This guide is designed to streamline your workflow, making data management more efficient and user-friendly.
By simply dragging and dropping files into a designated folder and refreshing, all the files within that folder get consolidated seamlessly and a clickable link appears next to your data should you want to quickly jump to that source file.
I demonstrate this at the start of the video, and the entire how to process is covered off:
If you need to amend any data (like the 'March' figure in our example), a click on the hyperlink takes you directly to the file for editing. Post-editing, a quick refresh updates the consolidated data in seconds.
Navigating SharePoint in Excel
I've done several videos on the process of connecting to a SharePoint folder The trick is to use the correct path and authenticate with an organizational account.
Once connected, it's necessary to bypass the 'combine' button and head straight for 'transform data.' This step is essential for getting the complete list of files in your SharePoint site.
A key strategy here is to change 'SharePoint.Files' to 'SharePoint.Contents' for a more refined navigation experience. This adjustment makes it easier to manage and edit your data in the future. It also speeds up your refresh significantly. You do however need access to the entire SharePoint site path for this approach to work.
领英推荐
Implementing Hyperlinks in Power BI
The great news is that the process of creating hyperlinks and consolidating data isn’t confined to Excel. These functionalities extend to Power BI as well. You can simply copy your query from Excel and paste it into Power BI’s Power Query.
In Power BI, you can enhance your data presentation by showing hyperlink icons, which adds a visual and interactive element to your data sets.
To cap it all off, categorizing your data as 'Web URL' and using the appropriate formatting options makes your data set not only more visually appealing but also more functional.
Next Steps
If you want to always get the latest videos then subscribe at Access Analytic - YouTube
Wyn
Dynamic Excel Development; Optimized Excel Solutions for Business: Founder & Excel Developer @ Excel and Access, LLC | Dynamic Excel, Power Query, Access, SQL, QuickBooks, VBA: Programming, Training, Mentoring Services.
10 个月Yes, this is goooooooodddd stuff ;-)
Data Analyst | Data Visualization | Business Intelligence | Financial Analyst
11 个月Thanks Wyn! I find this process needlessly cumbersome without your assistance. SO much better to have source files on SharePoint instead of on networks drives!