Cara menggunakan google sheets power query

Ekaterina Pechyonkina (Ablebits Team) says:

Hello Lauren,

Thank you for your question. Please try . It will auto-update your summary table whenever records change in source sheets and if data appears or gets removed from the original tables. Hope this will do the job for you.

Natalia Sharashova (Ablebits Team) says:

Hello Rick,

Thank you for your question.

To consolidate your data by both column and row labels, please make sure to tick off both options on Step2: 'Use header label' and 'Use column label'.

As for links to source data like in our Combine Sheets Wizard for Excel, I'm afraid it's impossible to do the same in Consolidate Sheets. Since the latter calculates all numbers rather than pulls data, the result is the calculated summary of all sheets and there's simply nothing to refer to. However, this feature is already on the developers' list for Combine Sheets add-on for Google Sheets.

Mary Trifuntova (Ablebits Team) says:

Hello Rick,

I just wanted to let you know that we've just released an updated version of our Combine Sheets add-on with a new way to combine sheets – using a formula. Since the formula is connected to your original files, all changes made in the source sheets will be automatically reflected in the resulting table.

Please visit the help page for more details and feel free to try the tool out. If you have any questions/difficulties/suggestions, please reach us at [email protected]. We'll be glad to hear back from you 🙂

The Power BI desktop app provides several native connectors to different data sources, including Excel files, BigQuery, Google Analytics, QuickBooks Online, etc. However, you won’t see Google Sheets on this list. Nevertheless, it’s very doable to import Google Sheets into Power BI without many resources involved. In this article, we’ll introduce the two most actionable options for you to consider. 

Table of Contents

How to get data from Google Sheets to Power BI

There is no direct connection between Power BI and Google Sheets, but there are two workarounds to do the job:

• Import data from an Excel workbook. In this case, you’ll need to synchronize your Google Sheets spreadsheet with your Excel workbook stored on OneDrive and then import data from it.

• Import data from a web page. In this case, you’ll need to publish your Google Sheets spreadsheet as a web page and then import data from it.

Let’s check out both solutions in detail.

How to integrate Google Sheets with Power BI using Excel 

The first option includes two steps:

• Connect Google Sheets to Excel 
• Connect Power BI and Excel

Google Sheets -> Excel -> Power BI

Step 1: Connect Google Sheets to Excel

Note: works for Excel files only stored on OneDrive. 

This can be easily done with Coupler.io, a solution for automated data import from multiple sources to Excel, Google Sheets, or BigQuery on a schedule. In our case, Google Sheets is the data source where we will take data, and Excel is the data destination to load data to. Sign in to Coupler.io using your Google account and click Add new importer. Then take the following steps:

Set up source (Google Sheets)

• Choose Google Sheets as a source application. 
• Connect to your Google account
• Select your Google Sheets spreadsheet and a sheet to load data from.
• Optionally you can select a specific range to import.

Set up destination (Excel)

1. Choose Microsoft Excel as a destination application. 
2. Connect to your Microsoft account.
3. Select an existing Excel Workbook on OneDrive and a worksheet to load data to.
4. Optionally you can select the first cell where to import your data from Google Sheets.
5. Select an Import mode:
   1. Replace – to fully replace all data on the sheet
   2. Append – to add new data below the existing data on the sheet.

Set up schedule 

The last step is to enable the Automatic data refresh and customize the schedule for syncing data between Google Sheets and Excel.

Click Save and Run when you’re ready, and you will have your Google Sheets records in Excel. 

Now, we need to link this Excel workbook to Power BI.

Step 2: Connect Power BI and Excel

On the Home ribbon of your Power BI desktop app, click Excel Workbook.

Select the Excel file from your OneDrive folder. Then, in the Navigator window, select the worksheet to load data from and click Load.

There you go!

Read our detailed guide on how to connect Excel to Power BI.

Now, let’s check out the second solution for using Google Sheets with Power BI

Import data from Google Sheets to Power BI

There is another solution that allows you to integrate with Google Sheets. It consists of these two steps:

• Publish a Google Sheets spreadsheet to the web.
• Get data from the Google Sheets published link into Power BI. 

Step 1: Publish Google Sheets to the web

• In your Google Sheets file, go to File => Publish to the web.

• Select the entire document or a specific sheet to publish and click Publish.

• Copy the link to the published Google Sheets spreadsheet.

Step 2: Get data from the Google Sheets published link into Power BI

Now you can share your Google Sheets with Power BI. 

• For this, in your Power BI desktop app, go to Get data => Web.

• Insert the link to your published Google Sheets doc and click OK.

• In the next window, you need to choose how to access the web content: anonymously, using credentials, API key, or organizational account. The easiest way is to choose the anonymous access and click Connect.

• After the connection to the web page is established, the Navigator window will open. Since our Google Sheets spreadsheet has several sheets and we published the entire document, Navigator displays a set of HTML tables fetched from the web page, as well as a set of suggested tables based on the fetched HTML content.  

• Table 1 corresponds to the Sheet that we want to import to Power BI. So, we select it and click Load.

There you go! Your Google Sheets records are imported into Power BI.

However, in the imported dataset, the column headers have not been recognized, and three new rows were added from the top. This can be fixed before loading data if you click Transform Data. A Power Query Editor will open where you will be able to tidy up your table.

Click the Save icon in the top left and apply the changes you made. Now your Google Sheets table looks right in Power BI.

Now let’s answer a few questions you may have.

FAQ #1: Can I connect Power BI service and Excel?

Power BI service seems to provide a direct connection to OneDrive. And, apparently, users can easily connect their Excel files on OneDrive to Power BI. However, we failed to do this. Here is the flow we used: 

• Click Get data, then click Get in the Files block.

• Select OneDrive Personal or OneDrive Business depending on the Microsoft account you used for syncing Google Sheets and Excel.

• Choose the Excel file on the OneDrive folder and click Connect.

 And here is the result:

So, we recommend you load data from Excel files to the Power BI desktop. Then you’ll be able to publish your dataset to make it accessible via the Power BI service. We explained how to do this in our Power BI BigQuery connection guide.

FAQ #2: Will the dataset be refreshed automatically?

Importing data from Google Sheets to Excel can be automated on a custom schedule. Power BI automatically refreshes data from Excel on OneDrive about every hour. You can always manually refresh the dataset by clicking the respective button on the Home ribbon.

FAQ #3: How to connect Power BI to Google Sheets?

You can only export data from Power BI reports as Excel or CSV. If you want to load data from Power BI to Google Sheets, you’ll need to complete two steps:

• Download a .csv or .xlsx file to your device
• Upload the file to Google Sheets

Step 1: Export Power BI to Google Sheets

• In Power BI Desktop or Power BI Service, choose a report to export data from, click on More Options, and select Export data.

• Choose which data you want to export:
  • Data with current layout. You can export data in the same layout you see now without formatting only as an Excel file.
  • Summarized data. You can export the summarized data in either Excel or CSV formats.
  • Underlying data. You can export the raw data used to calculate the data in your visual.

Click Export => the file in the chosen format will be downloaded to your device.

Step 2: Bring data from Power BI to Google Sheets

• Open a Google Sheets file, go to File => Import.

• Click Upload and select the file exported from Power BI.

• Choose the desired Import location then click Import data.

That’s it. Your Power BI data is now available in Google Sheets.

FAQ #4: Сan you directly connect Power BI to Google Sheets?

Truth be told, this is possible programmatically with the help of the Power BI REST API. However, this would require you to register and authorize an app for Power BI and write code in Apps Script, Python, or another language to send requests to the API. 

To do this without coding, you need to have a Power BI to Google Sheets integration similar to the ones that Coupler.io provides. If you badly need such an integration, let us know about this by filling out this form. Coupler.io is developing all the time and your voice can be crucial in making a decision for the next integration to be released.

Which is the best way to connect Power BI and Google Sheets

Since we introduced two methods for importing Google Sheets to Power BI, a logical question arises: Which method is the best one? The method of publishing Google Sheets to the web seems to be the easiest one. However, this makes your data publicly accessible. Besides, you’ll have to use Power Query Editor to transform your data before loading it to Power BI.

In the case of the Coupler.io method, the flow looks quite smooth. However, the data won’t be refreshed live, but only on a schedule that you’ve set up (hourly, daily, etc.). 

Langkah langkah cara mengakses Google Spreadsheet?

Langkah memasukkan data ke Google Sheet?

Apakah rumus excel dan Google sheet sama?

Apa saja fitur utama dari Google Sheets?