# View & Edit Power Query in Tabular Editor > Tabular Editor is not only a helpful tool for developing DAX. You can also use it to view & modify your Power Query M code, without ever opening Power BI desktop. Furthermore, there also exists a formatter for Power Query that can help you make your code easier to read & manage. Here, I walk through the basics, the use-cases, and a small tutorial on how to do this, yourself. - URL: https://data-goblins.com/edit-power-query-outside-power-bi - Author: Kurt Buhler - Published: 2022-02-08 - Topic: Tools and automation # EDIT POWER QUERY WITHOUT THE .PBIX *…so you can **manage your Power BI dataset entirely from Tabular Editor**. No more Power BI Desktop!* --- ## SUMMARY **In this article, you will learn:** - [**The Mash-up (M) Language**](https://docs.microsoft.com/en-us/powerquery-m/) sits behind Power Query data transformations, and provides you more power & flexibility in data transformations when adjusting or authoring your own M code. - It’s sometimes necessary to modify Power Query of a **model without opening the .pbix**. - In [**Tabular Editor 2**](https://github.com/TabularEditor/TabularEditor/releases)** (TE2) & **[**Tabular Editor 3**](https://tabulareditor.com/downloads)** (TE3)**, you can view & edit the Power Query M formula language from the** ‘partition’ or ‘shared expression’ **objects of the *TOM Explorer***.** You cannot edit M code while connected to a Power BI desktop file, though, unless unsupported features are enabled. - In the [**metadata file formats**](https://data-goblins.com/version-control) (*.bim* and *database.json* folder structure) it is also possible to **manually view & edit Power Query M code**, then deploy changes to the service. - You can [**automatically format Power Query M code** using the Power Query Formatter](https://www.powerqueryformatter.com/formatter) by mogular GmBH ([github link](https://github.com/mogulargmbh/m-formatter)).

View & edit Power Query M code from Tabular Editor 2 or 3 in ‘ Partitions ’ in the TOM Explorer

--- ## BEFORE YOU START **In this article, it’s assumed:** - You have installed either [**Tabular Editor 2**](https://github.com/TabularEditor/TabularEditor/releases)** (TE2) or **[**Tabular Editor 3**](https://tabulareditor.com/downloads)** (TE3)**. - You are familiar with **.bim** or** database.json** (folder structure) **metadata formats** for tabular models. [**If not, I recently wrote an article about it here**.](https://data-goblins.com/power-bi/learning-tabular-editor-part-1-beginner/#formats=) - You have a **Power BI Premium** or **Premium-Per User Capacity** to be able to connect to and modify deployed datasets using **XMLA endpoint connectivity** (where read/write is enabled) --- ## THE BASICS ### Power Query & the Mash-up Language

Power Query logo

**One of the most powerful native tools at your disposal when working in Power BI is** [**Power Query**](https://docs.microsoft.com/en-us/power-query/power-query-what-is-power-query)** (PQ)**. Power Query is used to** extract, transform and load (ETL) data** into the tabular model created in Power BI Desktop. Power Query exists not only in Power BI, but also in other tools like Excel and other parts of the Power Platform, and has a Power Query online component in both Power BI Service (Dataflows) and Azure Data Factory. The name‘Power Query’ refers to both the engine and the user interface provided in tools that use it, which allows for easy, effective data transformations. Often ‘Power Query’ is used to refer to the underlying language, as well, although this is actually the [**Mash-up (M) formula language**](https://docs.microsoft.com/en-us/powerquery-m/). All the transformations done in Power Query are translated to an M script, which you can see if you enable the *formula bar* in Power Query (under View) or when selecting the *Advanced Editor*, which will expose the entire M script for the query. The script can also be manually edited to produce a desired result you would otherwise not be able to do with the user interface. --- ### Why adjust or author your own M code? Examples

Below are two simple examples for when you would **write your own Power Query M code** to get a result not possible from the PQ user interface. In these examples, you rely on *Table.ColumnNames()* function to get a list of the column names, referring to the list instead of explicit column names, themselves. This can be helpful in certain use-cases to avoid errors when referring to column names of flat file (Excel, CSV) sources that might change, or if you prefer to handle column naming using indexes (i.e. like some may prefer in Python). These can be extended to do a variety of other useful data transformations. They are just listed as examples to illustrate why exploring the M language for Power Query is so valuable. ### Example 1: Change all column data types This is an example code snippet that changes all **column types to text without referring to column names**. This may be helpful if working with an Excel or CSV file with multiple users that make regular changes. If only using the UI, the ‘Changed Type’ step will refer to the column names. If the names are changed, the query breaks. ***Version 1.1:** Change types with the UI* *This version is not optimal because the header for “Column 1” changes often, breaking the query.* ***Version 1.2:** Add MissingField.Ignore argument* *This version makes it so that if “Column 1” is not found, the query can still complete.* *It is still sub-optimal, because the changed column won’t have the right datatype. Worse, we won’t know, since an error message wasn’t thrown.* ***Version 1.3:** Changes all column types* *This version will help because the columns will always have the right type conversion, even if the headers change or if new columns are added; but, those new columns will also be ‘text’ datatype.* ### Example 2: Renaming columns by index Similar to example 1, this code snippet avoids explicitly naming the columns in an early step; they are **re-named instead by their position (index) in the source table.** This has its own disadvantages, of course, as it’s susceptible to errors if the column orders change in the source data. Depending on your use-case, you might prefer column names, or indexes. ***Version 2.1:** Change names with the UI* *This version refers to columns by their names rather than indexes.* ***Version 1.2:** Ad MissingField.Ignore argument* *Similar to the left example, we can avoid an error thrown when a column is not found. Columns not specified won’t be renamed, but the query will continue. Since no error message is thrown, we won’t know that this happened.* ***Version 1.3:** Rename by column index* *This version refers to columns by their index in the Table.ColumnNames() list created as a separate “variable” step.* > **Tip** Adding comments with // before the #”Step” in Power Query will produce a description that you can also see in the “Applied Steps” UI. This can also be done in the UI itself by manually adding a description to that step.

Descriptions in PQ are comments above the step in M

--- ### Learning the M language is worthwhile for Power Query

Experiment with the formula bar & intellisense to start to learn the M language

As mentioned already, there are many use-cases for adjusting the M code in the advanced editor, or writing your own M script, yourself. In general, this affords[**more flexibility**](https://www.youtube.com/watch?feature=youtu.be&v=09tvia_8ykI) and [**sometimes even better performance**](https://blog.crossjoin.co.uk/2021/02/21/query-folding-on-sql-queries-in-power-query-using-value-nativequery-and-enablefoldingtrue/) of a given query. It’s recommended that you try experimenting with the M script in the formula bar for a given step, so you learn. There,** intellisense code-assist **is enabled, to help you know what functions to select and which parameters they need. If you haven’t yet done so, [**it’s worthwhile familiarizing yourself with the basic M functions and examples of their use.**](https://docs.microsoft.com/en-us/powerquery-m/opbuildpdf/toc.pdf?branch=live)** --- ## VIEW & EDIT POWER QUERY M CODE OUTSIDE OF POWER BI ### Use-cases Once you’ve developed your Power BI model, it is typically deployed to the Power BI service for testing and consumption by end-users. it is possible to connect to these data sources using XMLA endpoint, which enables both read and (on Premium & Premium-Per User capacities) write operations. This is very powerful for external tools like [**DAX Studio**](https://daxstudio.org/downloads/) (read),** SQL Server Management Studio** (SSMS; read/write), [**ALM Toolkit**](http://alm-toolkit.com) (read/write) and [**Tabular Editor**](https://docs.tabulareditor.com) (read/write), among others. It’s commonly understood that you can develop DAX code using such tools, but what about Power Query? *There are many reasons why you may want to do this, such as…*

A. You don't have access to the .pbix

It might be that you need to make a change in Power Query, but do not have access to the .pbix. Perhaps the latest .pbix is outdated, or even corrupted.

B. You cannot (performantly) open the .pbix

I have seen cases when Power BI files become so large and the relationships have such a high cardinality that opening the .pbix freezes the laptop every time. Such ‘monster models’ consume all available resources and make basic checks & developments a difficult, if not impossible. If you aren’t fortunate enough to benefit from a virtual machine or better hardware, you might need to connect to either the extracted metadata or deployed dataset.

C. Your workflow is mainly via Tabular Editor

I regularly hear people assume that external tools only allow you to develop & modify DAX code. While this is certainly the most prevalent use-case - particularly with the numerous code assist features - it is still **easy to modify Power Query from Tabular Editor, or deploy Power Query changes with ALM Toolkit.** If you are switching to using Tabular Editor & metadata files as the keystone for your development workflow, this is important to know. **You can use Tabular Editor to develop entire models from scratch, end-to-end, without ever opening Power BI Desktop.**

### A. You don't have access to the .pbix It might be that you need to make a change in Power Query, but do not have access to the .pbix. Perhaps the latest .pbix is outdated, or even corrupted. ### B. You cannot (performantly) open the .pbix I have seen cases when Power BI files become so large and the relationships have such a high cardinality that opening the .pbix freezes the laptop every time. Such ‘monster models’ consume all available resources and make basic checks & developments a difficult, if not impossible. If you aren’t fortunate enough to benefit from a virtual machine or better hardware, you might need to connect to either the extracted metadata or deployed dataset. ### C. Your workflow is mainly via Tabular Editor I regularly hear people assume that external tools only allow you to develop & modify DAX code. While this is certainly the most prevalent use-case - particularly with the numerous code assist features - it is still **easy to modify Power Query from Tabular Editor, or deploy Power Query changes with ALM Toolkit.** If you are switching to using Tabular Editor & metadata files as the keystone for your development workflow, this is important to know. **You can use Tabular Editor to develop entire models from scratch, end-to-end, without ever opening Power BI Desktop.**

Inherited ‘monster models’ can sometimes be problematic to audit & explore in Power BI Desktop

### Power Query in Metadata files For example, a common (and recommended) workflow for Tabular Editor is working from the metadata files, either the **.bim** file or the **database.json** folder structure. These are first created by connecting to the model and [**saving it from Tabular Editor**](https://data-goblins.com/power-bi/learning-tabular-editor-part-1-beginner/#formats=), or by [**using pbi-tools**](https://pbi.tools/tutorials/getting-started-cli.html)** on the .pbix**. This is to facilitate more granular source control of your Power BI model, [**which I have explained in a previous set of articles, here**](https://data-goblins.com/version-control). When working from the metadata files, though,** what do you do if you need to make a change in Power Query**? What if you want to implement or change incremental refresh without Power BI Desktop? In fact, the M code is actually easily visible and editable in these metadata files. It is stored chiefly in two places: - **Partitions:** This is where the M code is for objects that are loaded into the model. - **(Shared) Expressions:** This is the M code for objects not loaded into the model, like parameters (i.e. *RangeEnd, RangeStart* for incremental refresh) or tables which have *‘load to model’* disabled.

…as well as in the serialized folder structure

In the above metadata files, it is technically possible to directly change the Power Query M code in the array for “expression”. Once these changes are saved, the metadata can be opened & deployed to the service using other tools like Tabular Editor or ALM Toolkit. Modifying the metadata files is obviously not a pragmatic or recommended way to do this, though. **Instead, you can much moreeasily do this from the user interface of Tabular Editor.** ### Using Tabular Editor & the TOM Object Explorer In both TE3 and TE3, the M code is easily visible either when looking under*** “Shared Expressions”*** or the respective** *“Table name > Partitions”***. From here you can edit the code and then save the changes to the metadata files or even the Power BI service. Note that you cannot save changes to the M expression when connected with an open Power BI Desktop model (.pbix), unless unsupported operations are enabled, but this is not recommended, as it can cause issues with your file.

[![ TE2 ](https://images.data-goblins.blog/2025/12/edit-power-query-te2-logo.png)](https://github.com/TabularEditor/TabularEditor/releases/) TE2

View in TE3 is almost identical, save for extra formatting & UI customization

[![ TE3 ](https://images.data-goblins.blog/2025/12/edit-power-query-te3-logo.png)](https://tabulareditor.com/) TE3 If you have incremental refresh enabled, you will notice multiple partitions, where the M expression is not fully visible in the expression editor. Instead, you will see in the *Properties *window the menu **Refresh Policy**. This contains information about how your data should be refreshed. There, under** Source Expression**, you also find the M code. You can even [**set up incremental refresh from scratch in Tabular Editor 2 and 3**](https://docs.tabulareditor.com/te2/incremental-refresh.html#setting-up-incremental-refresh-from-scratch-with-tabular-editor) using this menu. However be aware that unlike Power BI Desktop, Tabular Editor cannot inform you whether your [**query is folding to the source database or not**](https://docs.microsoft.com/en-us/power-query/power-query-folding), so it’s important to be aware of** when query folding does and does not happen** for the source you are using. What if you want to add new columns, or new queries altogether? When adding new columns, you change the schema of your table. **Tabular Editor 3** has a feature called ‘**Update Table Schema**’ which is meant just for this:

Updating the schema automatically or manually in TE3

- Once you’ve saved your changes, right-click the table - Select ***‘Update Table Schema…*’** - The query will be executed to retrieve the metadata - A summary of the schema changes will be shown, if any are detected - Sometimes, TE3 is unable to retrieve the metadata from the service, and will return an error. In this case, **it is possible to modify the schema manually by creating or modifying the existing data columns** in the table from the TOM Explorer. When creating new queries, the easiest way is to copy existing queries, then modify the expression and update the schema. It is also possible to add tables using the ***Table Import Wizard*** &** *Data Sources***, but this is a concept that I will discuss in later tutorials for Tabular Editor. ### Exporting M Code from Tabular Editor

C# Script to export the M code (expression) from a table partition

It is also possible to export the M code from [Tabular Editor and other tools using the Tabular Object Model library](https://docs.tabulareditor.com/te2/Useful-script-snippets.html#export-object-properties-to-a-file) (TOM). Using C# scripts, you can refer to the objects and even write them to a file output. This can be context-sensitive, so you can write C# scripts for macros in TE3 or custom actions in TE2 to export expressions and properties for what you select.

Note that the result will include carriage returns (\r) and new line (\n) characters, as well as others (\nin) that you should replace / format out, for the script to be readable.

--- ## An example from other tools: DAX Studio ### View & Export Power Query M code with DMVs [![ DAX Studio ](https://images.data-goblins.blog/2025/12/edit-power-query-dax-studio-logo.png)](https://daxstudio.org/downloads/) DAX Studio Another way to read (not write) and extract the M expressions from your model is using DAX Studio. Be it for documentation or governance purposes, you might wish to extract expressions from your model. This is possible with both DAX and M code, as well as other model metadata. Just like DAX, Power Query M code is also visible in both the [model Dynamic Management Views (DMVs)](https://docs.microsoft.com/en-us/analysis-services/instances/use-dynamic-management-views-dmvs-to-monitor-analysis-services?view=asallproducts-allversions#:~:text=Analysis%20Services%20Dynamic%20Management%20Views,server%20operations%2C%20and%20server%20health.&text=DMV%20queries%20return%20information%20about,%2Dtime%2C%20use%20tracing%20instead.) as well as [the Tabular Object Model (TOM) for XMLA endpoint](https://docs.microsoft.com/en-us/analysis-services/tom/tom-pbi-datasets?view=asallproducts-allversions#:~:text=The%20Tabular%20Object%20Model%20(TOM)%20is%20a%20.,Table%2C%20Column%2C%20and%20Measure.). It is possible to extract all Power Query expressions from the model in **DAX studio**, for example using the DMVs. You are rather limited in how you can filter and explore this data, so typically people extract it to flat files and explore / document it in other tools.

Using a pseudo-SQL syntax to query the model DMV for a specific partition, to see the M code

--- ## Formatting your Power Query M Code ### Tabular Editor A common best practice for authoring code is formatting it following consistent conventions. For DAX, this is trivial thanks to the efforts of the **sqlbi team** to create [**daxformatter.com**](https://www.daxformatter.com) which is** integrated in tools like DAX Studio & Tabular Editor**, which can [automate this for you](https://data-goblins.com/format-all-dax). Unfortunately, however,** there are no formatting or code-assist features for authoring Power Query M code in Tabular Editor**, like for DAX. [However, this may be on the future roadmap](https://docs.tabulareditor.com/te3/whats-new.html). [![](https://images.data-goblins.blog/2025/12/edit-power-query-formatter.png)](https://www.powerqueryformatter.com/) ### Power Query Formatter Until then, there does appear to be a less well-known formatter specifically for M code. Adopting a familiar name, [**powerqueryformatter.com**](https://www.powerqueryformatter.com)works similarly to DAX formatter; you input the code and it will beautify the script, for you.** You can then copy & paste this back into your application, like Tabular Editor, to save the changes to the metadata files or model.** There is also an API available, but this is not yet integrated in any external tools; however, I’d guess it’s only a short matter-of-time before this happens. The Power Query formatter is created by ***mogular GmBH** *([github link](https://github.com/mogulargmbh/m-formatter)). > **Note** As with all external tools, it’s important to be conscious of what you are submitting to an external tool or service. Thus, it’s advised that you remove any non-parameterized connection strings or hard-coded, sensitive code elements (which is a bad practice, anyway) before using a code formatter like this. Use any third-party service or external tool at your own risk, after assessing it yourself. --- **You’ve now learned:** - **The Mash-up language (M code) sits** behind Power Query - **Modifying / authoring M code** can be helpful for getting & transforming data effectively - You can read & modify Power Query M code **outside of Power BI Desktop** - External tools like **Tabular Editor and DAX studio** make this easier - You can beautify your M scripts using **powerqueryformatter.com**

Tools & knowledge to help you be more productive

---