Table of Contents
A free online spreadsheet programme called Google Sheets is available through the Google Drive service. Additionally, the programme is accessible as a ChromeOS desktop application and a mobile app for Android, Windows, iOS, and BlackBerry. Other Google products like Google Docs, Slides, and Forms are also hosted by the Google Drive service.
Microsoft’s technology-driven business intelligence tool, Power BI, analyses and visualizes raw data to provide usable knowledge. It brings together business analytics, data visualization, and best practices to support an organization’s decision-making using data. Because of the capabilities of the Power BI platform, Gartner recognized Microsoft as the Leader in its “2019 Gartner Magic Quadrant for Analytics and Business Intelligence Platform” in February 2019.
Microsoft offers a business analytics service called Power BI that enables you to share insights and view data. In order to create dynamic dashboards and Business Intelligence reports, it transforms data from many sources. Power BI has access to enormous amounts of data from various sources. It enables you to view, analyze, and visualize sizable amounts of data that Excel is unable to open. Power BI supports a number of significant data sources, including Excel, CSV, XML, JSON, PDF, etc. We mentioned below are the ways to Use Google Sheets Data in Power BI.
Ways to Use Google Sheets Data in Power BI
Publish your Google Sheets as a web page
- Firstly you must share the document. Choose “Anyone with the link can VIEW”
- After that tab on the menu choose “File” => “Publish to the web”
- Select “Entire document” and “Web page”, click Publish
Enter the data into Power BI
- First Open Power BI Desktop and create a new file
- Tab on the button “Get data” at the top and choose “Web” as source.
- In dialogue box, choose one of the URLs from Google Sheets
- In data source dialogue box, choose the Google Sheets URL as a data source
- In “Navigator”-popup, notice that the data lies in “Table 0”. Check the table and click the “Load”-button.
- The fields pane after adding first table
- You have loaded your first table. In the Fields panel it will look something like the image on the right. Rename the Table “Exercise”.
- Do same procedure with the other 4 tables and rename them accordingly.
Clean the data
- Tab “Edit queries” in the top menu to open the Query Editor.
- Select “Exercise”-table
- Then choose “Use First Row As Headers” in the top menu. As you might notice, in the “Applied steps”-panel, a new row “Promoted Headers” will appear. If you make a mistake you can always remove steps here
- Remove first column that is empty and the second column with the primes, and delete all the null columns on the right.
- Correct decimals. If you have columns containing decimal numbers, you may need to perform a simple replacement action. (Google uses ‘.’ (period) while Power BI uses ‘,’ (comma). This may be different in other language editions. In any case, we need to replace all decimals with periods with commas. Choose the column(s) that have decimal numbers. Choose the “Transform” tab at the top and click on “Replace values“. Now replace ‘.’ with ‘,’. Note that the Datekey column must also be an integer.
- In case rest of the columns should have type “Whole number”
- We do same for our other tables. (In the time table, the Datekey column should be an integer, while the Date column, which should have a real date format, should have the Date format. The other fields such as Year, Month and Day should normally be text fields, while the offset fields should be integers).
Final Words
We hope you like our article on how to Use Google Sheets Data in Power BI. Due to its freeness and simplicity of use, Google Sheets is becoming increasingly popular. The fact that the most recent version of Microsoft Edge integrates with Microsoft 365 and has a dashboard that resembles Google Drive suggests that Microsoft is feeling the effect of Google. You might need to import data from Google Sheets into Microsoft Power BI because so many people and even businesses are using Google Sheets.