top of page
Search

Finding Table.RemoveColumns function in M Code in Power Query Editor Using TMDL View

  • Jihwan Kim
  • Mar 9
  • 4 min read

In this blog post, I’d like to share how I learned to find where Table.RemoveColumns function in M code is used in Power Query Editor and why replacing it with Table.SelectColumns can be a more efficient approach.



The Challenge: Handling Column Removal from Data Source

Imagine the data source team decides to remove some columns from a table in the source.

If those columns were explicitly removed using Table.RemoveColumns in Power Query Editor, the query might break when those columns no longer exist in the source. This happens because Table.RemoveColumns expects the specified columns to be present in the source, and if they are missing, it results in an error.

To avoid this issue, using Table.SelectColumns instead can be a safer alternative. With Table.SelectColumns, I can define the columns I want to keep, ensuring that if a column disappears from the source, the query still functions without errors.



Understanding Table.RemoveColumns vs. Table.SelectColumns

Both functions are used to shape the data by selecting or removing columns in Power Query Editor, but they behave differently:

1. Table.RemoveColumns

  • Removes specified columns from a table.

  • If the specified columns do not exist in the table, an error occurs.

  • Useful when you know the unwanted columns will always be present in the data source.

Example:

let
    Source = Table.FromRecords({
        [ID = 1, Name = "A", Age = 45],
        [ID = 2, Name = "B", Age = 50]
    }),
    RemovedColumns = Table.RemoveColumns(Source, {"Age"})
    
    // If "Age" column is missing in Source, this will cause an error.

in
    RemovedColumns

2. Table.SelectColumns

  • Selects specific columns from a table.

  • If a column is missing, Power Query does not generate an error; it simply excludes it.

  • Recommended for situations where column existence is not guaranteed.

Example:

let
    Source = Table.FromRecords({
        [ID = 1, Name = "A", Age = 45],
        [ID = 2, Name = "B", Age = 50]
    }),
    SelectedColumns = Table.SelectColumns(Source, {"ID", "Name"})
    
    // Even if "Age" disappears, this query will still work.

in
	SelectedColumns


Finding Table.RemoveColumns function in M Code in Power Query Editor Using TMDL View

One of the ways I discovered to efficiently locate Table.RemoveColumns in a complex M code in Power Query Editor is by using TMDL View. TMDL View helps analyze queries at a structured level, making it easier to track down specific M functions used across multiple queries.


Steps to Find Table.RemoveColumns Using TMDL View:

  1. Click on File > Options and settings > Options.

  2. Under Preview Features, enable TMDL View.

  3. Navigate to the TMDL View tab.


  4. Drag the whole semantic model into to the window.


  5. Use Find (Ctrl + F) to search for Table.RemoveColumns.


  6. The search results highlight all instances where Table.RemoveColumns is used in M code within the data model.


This method makes it significantly easier to locate where column-removal operations are defined, allowing for quick assessment and replacement with Table.SelectColumns if needed.



Example: How TMDL View Helped Me Find Table.RemoveColumns

Let’s say I have multiple queries, and I suspect that some use Table.RemoveColumns, which could cause errors if source columns are removed.

Instead of manually checking each query,

  • I opened TMDL View.

  • I searched for Table.RemoveColumns.

  • I found that it was used in three queries.

  • I quickly identified the affected queries and replaced Table.RemoveColumns with Table.SelectColumns where necessary.

This approach saved time and prevented potential data refresh failures.



Summary and Next Steps

What I Learned

  • Table.RemoveColumns can break queries if source columns are removed.

  • Table.SelectColumns is an alternative.

  • TMDL View is a powerful feature to locate M code operations efficiently.

  • This approach improved my workflow and reduced refresh failures.


Enhancing My Work Process

  • Implementing a best practice to use Table.SelectColumns instead of Table.RemoveColumns when appropriate.

  • Using TMDL View as a regular feature for Power Query models.

  • Taking it further with Fabric Notebooks: Automating the detection of Table.RemoveColumns usage across multiple Power BI semantic models.


Exploring Further with Fabric Notebooks

In Microsoft Fabric, similar searches can be done using Semantic Link Lab, a powerful library for interacting with Power BI semantic model.

Some useful functions include:


  • sempy.fabric.list_partitions( ) & sempy.fabric.list_expressions( ):


    sempy.fabric.list_partitions( ): Lists partitions in Fabric datasets. The [Query] column in the table generated by the code below displays the M code written for each query.


    sempy.fabric.list_expressions( ): Identifies expressions used in the dataset. The [Expression] column in the table generated by the code below displays the M code written for each query.


  • DAX Query: write INFO.EXPRESSIONS( ) and INFO.PARTITIONS( ) functions in Notebook: 

from sempy import fabric
import pandas as pd

result = fabric.evaluate_dax(
    workspace = "workspace name", dataset = "dataset name",
    dax_string=
    """
    EVALUATE
	VAR _expression = INFO.EXPRESSIONS()
	VAR _expressiontable = SELECTCOLUMNS(
		SUMMARIZE(
			_expression,
			[Name],
			[Expression]
		),
		"query_name", [Name],
		"m_code", [Expression],
        "dataset_name", "dataset name"
	)
	VAR _partitions = INFO.PARTITIONS()
	VAR _partitionstable = SELECTCOLUMNS(
		SUMMARIZE(
			_partitions,
			[Name],
			[QueryDefinition]
		),
		"query_name", [Name],
		"m_code", [QueryDefinition],
        "dataset_name", "dataset name"
	)
	RETURN
		
            UNION(
			_expressiontable,
			_partitionstable
		    )
    
    """
    )

df = pd.DataFrame(result)
spark_df = spark.createDataFrame(df)

# display result
display(spark_df)


These functions can help track M code operations beyond Power Query Editor, offering an even more comprehensive debugging approach.


By integrating these techniques, I can significantly improved my Power Query workflow, ensuring more resilient and maintainable data transformations in Power BI.


I hope this makes exploring TMDL View more enjoyable and insightful.

Commenti

bottom of page