Plenty of customers are turning to Google Sheets as a result of it’s free and simple to make use of. Microsoft might be feeling Google’s affect as a result of the newest model of Microsoft Edge interfaces with Microsoft 365, and the dashboard appears a bit like Google Drive.
With many customers and even organizations turning to Google Sheets, you may end up needing to import information from Google Sheets into Microsoft Energy BI. The potential to take action has been round for nearly a 12 months so it may be useful to learn the way. Thankfully, it’s straightforward.
SEE: Google Workspace vs. Microsoft 365: A side-by-side evaluation w/guidelines (TechRepublic Premium)
On this tutorial, I’ll present you the way to import information from Google Sheets into Microsoft Energy BI. We’ll additionally evaluate the way to import Microsoft Excel information into Google Sheets and vice versa. I’m utilizing Google Sheets in Microsoft Edge and Microsoft Energy BI on a Home windows 10 64-bit system. You possibly can obtain the demo file for this tutorial.
add and save an Excel file as a Google Sheets file
Google Drive enables you to add and retailer an Excel file with out forcing you to transform the .xlsx file to a Google Sheets file. In spite of everything, Google Drive is about storage, not format. Later, should you determine to save lots of the .xlsx file as a .gsheet file, Google Sheets received’t change the Excel file. Relatively, it creates a brand new Google Sheets file — you find yourself with an .xlsx and .gsheet file in Google Drive.
To reveal, let’s add an Excel file into Google Drive after which reserve it as a Google Sheets doc:
1. Open your Google Drive.
2. Click on the New button.
3. Within the ensuing dropdown, select File Add. You can too add a folder, which is handy, however we received’t accomplish that now.
4. Find after which choose the Excel file you wish to add and click on Open (Determine A). You should use one among your Excel information or the demonstration PBIGoogleSheets_ExcelDemo.xlsx file after saving it regionally.
Determine A
5. Within the ensuing submenu, select Open With, after which choose Google Sheets.
The file in Google Sheets is an editable Excel file. If you happen to make modifications to the file, Google maintains the .xlsx format. If you happen to determine to transform the information to Google Sheets, click on the File menu after which select Save As Google Sheets (Determine B).
Determine B
At this level, you could have two distinct information: One is an Excel file and the opposite is a Google Sheets file (Determine C). If you happen to make modifications to this file, Google won’t save them to the .xlsx file and vice versa.
Determine C
Now let’s have a look at the way to convert a Google sheet to an Excel sheet.
convert a Google Sheets file to an Excel file
Changing a Google sheet to an Excel file is even simpler as a result of you may obtain and alter the format on the identical time. To reveal, let’s convert the Google sheet we simply saved to Excel as follows:
1. Open the Google sheet.
2. Click on the File menu and select Obtain.
3. Within the ensuing submenu (Determine D), choose Microsoft Excel (.xlsx).
Determine D
Or right-click the Google Sheets doc and select Obtain. Google Drive will obtain and convert the Google sheet to an Excel .xlsx file, and also you’ll save a click on.
import Google Sheets file into Energy BI
The lengthy solution to get information in a Google Sheets file into Energy BI is to save lots of the file as an Excel file after which open that file in Energy BI. That’s not mandatory. To get Google Sheets information into Energy BI, you want solely the sheet’s URL in Google Drive. To reveal, let’s use the Google Sheets file from the earlier part as follows:
1. Return to or open your Google Drive.
2. Find the Google Sheets file that you just wish to use in Energy BI.
3. Copy the sheet’s URL (Determine E) to the Clipboard by choosing it after which urgent Ctrl + C.
Determine E
4. Signal into your Microsoft account and open Energy BI as you usually do. When making use of this to your personal work, open the .pbix file to which you wish to add the Google Sheets information. If you happen to at present have a .pbix file open, click on the File tab and select New to shut the open file to keep away from including this pattern information right into a working file.
5. Click on the Get Information tab.
6. From the dropdown, select Extra (Determine F).
Determine F
7. Within the ensuing window, enter Google into the Search management.
8. Within the record to the precise, choose Google Sheets (Determine G) after which click on Join. If prompted to permit a third-party to interface, click on Join.
Determine G
9. Enter the URL within the Clipboard (Determine H) by urgent Ctrl + V.
Determine H
10. Click on OK.
11. If prompted to signal into your Google account, accomplish that.
As you may see in Determine I, the Navigator shows all of the sheets within the Google Sheets doc.
Determine I
If you happen to’re utilizing the demonstration information, click on solely the Gross sales sheet after which click on Load. If prompted to save lots of pending modifications, click on Apply Modifications. Energy BI will convert the information into desk kind (Determine J).
Determine J
You continue to have a bit of labor to do:
- Take away the primary column, named Column 1. That is column A within the Google Sheets file.
- Left align the Date column.
- Apply forex formatting to the Quantity column.
Now that the information is in Energy BI, you need to use it as you’d every other information.
Getting Google Sheets information into Energy BI isn’t a whole lot of work. Merely copy the URL into Energy BI, load the information and begin constructing visuals.
