In this tutorial, Nick Lee from Pragmatic Works walks you through the process of connecting Power BI to multiple Google Sheets simultaneously. This task may seem tricky at first, but with Nick's guidance, you'll be able to pull data from multiple sheets within workbooks seamlessly and effectively.
Overview of the Problem
Nick Lee begins by explaining a situation he encountered during a virtual mentoring session, which turned into a consulting opportunity. A client needed help pulling data from multiple Google Sheets into Power BI for reporting purposes. The data was spread across several sheets in workbooks, and while the client had the necessary Power BI licenses, they did not have Google Looker for easier data management.
Step 1: Organizing the Sheets
- Create a table in Google Sheets listing all the links to the sheets you will access.
- Ensure each sheet's link is shared publicly by setting the share settings to "Anyone with the link."
- In Nick’s example, the sheets contain data such as numbers and letters in different formats across multiple sheets.
Step 2: Connecting to Power BI
- In Power BI, go to the "Get Data" option and select Google Sheets.
- Use the sharable link copied from the Google Sheet to connect it to Power BI.
- When connecting, Power BI may prompt you to sign in — do so using your credentials.
- After connecting, you'll be able to transform the data to clean it up and make it usable for your report.
Step 3: Modifying the URL for Export
- To facilitate the extraction of data, modify the URL by adding an “export=download” feature at the end of the link.
- Nick demonstrates how to add a custom column to the Google Sheets table, which will append this URL modification to each sheet’s link.
- This setup allows Power BI to download the data in Excel format, making it easier to work with.
Step 4: Automating Updates
- For automation, Nick suggests using tools like Power Automate, Logic Apps, or Azure Data Factory (ADF) to dynamically update the list of Google Sheets in the main table whenever new sheets are added.
- This makes the entire process much more dynamic, as new Google Sheets can be added without manually updating the table.
Step 5: Expanding Data for Reporting
- To pull data from multiple sheets and present it in one comprehensive table, Nick uses a block of M Query code that expands and combines the data from various Google Sheets.
- Once the data is expanded, users can easily visualize and report on it within Power BI.
- Nick walks through the code in detail and shows how to clean up any unwanted columns that may appear due to the code’s structure.
Step 6: Refreshing Data Automatically
- Once the system is set up, refreshing the data is as simple as hitting the "Refresh" button in Power BI. New data from any added sheets will automatically be pulled into the report.
- Nick demonstrates how adding a new sheet to the main Google Sheets table automatically updates the Power BI report, ensuring that data is always up-to-date.
Conclusion
By following Nick’s step-by-step process, users can successfully integrate Power BI with Google Sheets, regardless of how many sheets are involved. This solution makes it easier to consolidate and report data stored in Google Sheets using the robust features of Power BI, all without the need for additional licenses or complex tools like Google Looker. Thanks to Nick Lee for sharing this straightforward method!
Nick Lee from Pragmatic Works offers clear instructions and valuable tips for streamlining data integration between Google Sheets and Power BI.
Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power BI and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.