Power Query in Google Sheets: What Works and What Doesn’t

Power Query in Google Sheets: What Works and What Doesn't

No, Google Sheets does not include Microsoft Power Query or a built-in Power Query Editor. You can still import and reshape data in Sheets, but you will need its own tools, an external service, or a workflow that keeps Power Query in Excel.

What people mean by Power Query

Power Query is Microsoft’s data import and transformation technology. In Excel for Microsoft 365 on Windows, it is available through the Data tab, including the Get Data commands and the Power Query Editor. It can connect to supported files, databases, online sources, and other systems, then apply repeatable steps such as filtering rows, changing data types, splitting columns, merging tables, and removing errors.

A key feature is that those steps are recorded as a query. When the source data changes, you can refresh the query to apply the same transformations to the updated data. Power Query uses a language called M behind the scenes, although most routine transformations can be created through the editor’s menus without writing M yourself.

Google Sheets has no equivalent built-in editor that opens Power Query queries, runs M, and refreshes them as native Sheets queries. A spreadsheet may contain data that was prepared with Power Query, but that does not mean Sheets can operate the query that produced it.

Why Power Query does not run in Google Sheets

Power Query belongs to Microsoft’s data tools and is designed to work with products such as Excel and Power BI. Google Sheets is a separate spreadsheet application with its own formula system, import features, connectors, and automation options. Similar goals do not make the tools interchangeable: Sheets cannot take an Excel query definition and refresh it as if it were a native Google Sheets feature.

For example, an Excel workbook might contain a table populated by a query that combines monthly CSV files. If you upload that workbook to Google Drive and open it with Sheets, the visible table may still be useful, but the query’s source connections and transformation steps should not be assumed to work in Sheets. Conversion can affect workbook features. Keep the original Excel file and a copy of its output if the query itself is important.

This distinction also applies to Power Query’s M language. Sheets does not provide an M editor or an M execution engine. Copying M code into a cell, Apps Script project, or add-on will not turn it into a working Sheets query. Recreating a process means translating its logic into suitable Sheets tools or using a separate data-processing service.

What Google Sheets can do instead

Sheets includes several ways to bring in and prepare data, though each is narrower than a full query editor or better suited to a particular source:

Import a file: Use File > Import to bring in a spreadsheet or supported text file. This is convenient for a one-time load or a manual update, but it does not automatically reproduce a complex Power Query workflow.
Import data from a web page or URL: Sheets offers functions such as IMPORTDATA, IMPORTHTML, and IMPORTXML for certain accessible sources. They have different requirements and can be affected by website changes, access controls, and refresh limits.
Link another spreadsheet: IMPORTRANGE can bring a range from a different Google Sheets file into a destination spreadsheet after access is authorized.
Clean data in the interface: Tools such as Split text to columns and options under Data > Data cleanup can help with common tasks, including removing duplicates and trimming whitespace.
Automate a workflow: Google Apps Script can run custom JavaScript-based tasks, while third-party add-ons and external services may offer other connections or scheduled imports.

These options can be enough for a modest workflow. They are not one unified feature: a formula-based import, a manual file import, and a script each have different setup, refresh, error-handling, and maintenance requirements.

A practical way to move a Power Query workflow to Sheets

If a team wants the finished data in Sheets, start by describing what the existing query does rather than trying to copy its code. Identify the source, the steps that alter the data, how often it changes, and what the final sheet needs to show. A query that only removes blank rows from a small CSV is very different from one that combines folders of files and joins several database tables.

Inspect the output and the query steps. In Excel, note the source location, column names, filters, type changes, merges, and refresh settings. Record any credentials or access requirements separately; do not put passwords in a shared sheet.
Decide where the work should happen. If the source is a simple downloadable CSV, a manual import or a Sheets import function may be enough. If the process combines many sources or needs controlled, scheduled refreshes, use an appropriate data service or keep the transformations in Excel or another system.
Rebuild a small sample first. Bring in a representative set of rows and check that dates, numbers, empty values, and text are interpreted correctly. A small trial can expose differences before users depend on the new result.
Compare outputs. Check row counts, key totals, duplicate handling, and a few records that pass through important transformation steps. Matching column headings alone does not prove that two workflows produce equivalent data.
Set expectations for updating. Specify who refreshes or replaces the data, how often it should update, and what to do if an import fails. A sheet that only changes when someone manually imports a new file should not be described as automatically refreshed.

For instance, suppose an Excel query combines three monthly sales files, standardizes the date and region columns, and calculates a total by region. If Sheets only needs the final monthly summary, it may be simpler to export the prepared result from Excel and import it into Sheets. If the goal is to reproduce the entire pipeline in Sheets, you will need to recreate the file intake, cleanup, combination, and summary logic—and verify that the new process handles future files the same way.

When Sheets import functions are a good fit

Built-in import functions are most useful when the source is stable, accessible, and relatively straightforward. IMPORTDATA is intended for accessible CSV or TSV data; IMPORTHTML can retrieve tables or lists from a web page; IMPORTXML can retrieve data selected from XML or HTML using XPath; and IMPORTRANGE connects ranges between Sheets files. These are not general-purpose connectors for every website or database.

Web imports can fail when a site changes its markup, blocks automated requests, requires a login, or does not expose the information in a compatible format. A function that worked previously may also need to recalculate or be reauthorized. For external data, check the source’s terms and make sure you are allowed to retrieve and share it.

Import functions also do not provide the same visual sequence of transformation steps as Power Query. You can combine formulas and helper columns to clean data, but a large workbook of intertwined formulas may be harder for another person to understand and maintain. Document the source and the intended refresh behavior, and keep raw imported data separate from a cleaned or summarized output when practical.

Connected Sheets, Apps Script, and add-ons

For some Google Workspace accounts, Connected Sheets provides a way to work with BigQuery data from Sheets. It is a connection to Google’s analytics and data warehouse service, not a general Power Query replacement for arbitrary files and sources. Availability and capabilities can depend on the account, permissions, and Google Workspace setup. A user may need access to the relevant BigQuery data and project.

Apps Script is another option when the workflow needs custom behavior. A script can call supported services, transform values, and write results into a spreadsheet, subject to applicable permissions, quotas, and service limits. It requires someone to build and maintain code; it is not a point-and-click Power Query editor, and a script’s success depends on how its author handles errors, credentials, and changing source formats.

Add-ons and external data tools may connect Sheets to systems that its built-in features do not cover. Before relying on one, check its pricing, refresh schedule, access permissions, data storage practices, and whether it supports the exact source and transformation steps you need. A tool that can import a table may not be able to reproduce joins, folder-based ingestion, or complex cleanup.

Power Query in Excel versus data work in Sheets

Power Query is a stronger fit when data preparation involves many repeatable steps, multiple files, or sources that need to be combined before analysis. Its editor makes those steps visible and can refresh the result from supported sources. Excel for Microsoft 365 on Windows exposes it through Data > Get Data and the Power Query Editor. Exact connectors and capabilities can vary by Excel version, operating system, account, and source.

Sheets may be a better fit when people need to collaborate in a browser, make relatively simple changes, or use data already held in Google Workspace. Its sharing and live collaboration model is different from Power Query’s data preparation focus. A common arrangement is to prepare or consolidate data in Excel or a data platform, then share an output in Sheets for review or lightweight analysis.

Neither choice is automatically better. Consider where the source data lives, who must access it, how often it changes, how much transformation is involved, and what the organization permits. If users need the query steps to remain editable and refreshable, preserve the workflow in a compatible Power Query environment rather than assuming a converted spreadsheet will retain it.

Common problems and safeguards

The most frequent mistake is treating a pasted result as a live connection. Imported values can be a snapshot, while formulas and connectors may update under different conditions. Label the source and last-updated time so readers can tell whether they are looking at current data.

Other issues often come from differences in data interpretation. Dates may arrive as text, decimal separators can vary by locale, blank cells may behave differently from empty strings, and identifiers with leading zeroes can be changed if treated as numbers. Check important fields after importing, especially account codes, postal codes, dates, and currency values.

Keep an untouched copy of source data before cleaning or replacing it.
Test with representative records, including blanks, duplicates, unusual dates, and unexpected text.
Limit access to sensitive source data and review permissions before connecting an add-on or script.
Record who owns the refresh process and what a successful update should look like.

For a business-critical process, include a repeatable validation check—such as comparing expected row counts or a known total—and a documented fallback. A spreadsheet that silently shows an old import can be more misleading than one that clearly reports an error.

Frequently Asked Questions

Can I open an Excel Power Query workbook in Google Sheets?
Sheets may open or convert workbook data, but it does not provide a native way to run and refresh the workbook’s Power Query steps. Keep the original workbook if you need its query connections and transformations.

Can Google Sheets run Power Query M code?
No. Google Sheets does not have a built-in M language engine or Power Query Editor. You would need to translate the logic into Sheets features, write a separate script, or use another data tool.

What is the closest Google Sheets alternative to Power Query?
There is no single equivalent. For simple imports, use file import tools or appropriate import functions; for cleanup, use Sheets’ data tools and formulas; for custom automation, consider Apps Script; and for BigQuery data, check whether Connected Sheets is available for your account.

Can Sheets automatically refresh imported data?
Some connected or formula-based sources can update, but refresh behavior depends on the particular function, connector, source, and account. Do not assume that a manually imported file or every external page refreshes on a schedule. Confirm the behavior for your workflow.

Should I use Excel or Google Sheets for a data-cleaning workflow?
Choose based on the source systems, collaboration needs, transformation complexity, access controls, and refresh requirements. Power Query is useful for repeatable data preparation in compatible Microsoft tools; Sheets is convenient for browser-based collaboration and simpler workflows. A staged process using both can also make sense.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.