Power BI: Changing The Data Type of a Custom Column Using the M Language

In the world of Power BI, one of the most powerful and frequently used features for transforming data is the M Language, also known as Power Query. In this tutorial, Angelica Choo Quan, a trainer at Pragmatic Works, demonstrates how to change the data type of a custom column in Power BI using the M language. By following this guide, you will learn how to clean and shape your data more efficiently while minimizing the number of steps in your Power Query editor.

Why Change the Data Type of a Custom Column?

When you’re building a report in Power BI, especially during the data shaping phase, you often need to clean and transform your data. One key transformation involves changing the data type of a custom column that you create. This step ensures that the data is consistent and ready for analysis. Angelica walks us through how to do this in a seamless manner using M, which helps streamline the process.

Steps to Create a Custom Column in Power BI

Here’s how Angelica creates a custom column using Power Query in Power BI:

1. Load Your Dataset:

   In this example, Angelica uses a dataset containing information about airlines, including data on states, airports, and their performance.

2. Add a Custom Column:

   Navigate to the ribbon at the top of the Power Query editor and select the Add Column tab. Choose Custom Column from the available options.

3. Concatenate Columns:

   In the custom column window, Angelica demonstrates how to concatenate two columns: airport key and region. Using the & operator, she concatenates the airport key (a number) and region (text). However, as the columns contain different data types, Power Query throws an error.

Handling Data Type Errors

Power Query expects columns to have the same data type when performing operations like concatenation. If there is a mismatch, such as one column being a number and the other a text, an error occurs. Angelica shows us how to fix this issue by changing the data type of one of the columns before performing the operation.

Here’s how to fix the error:

1. Change the Data Type:

   In the custom column window, open the applied steps pane by clicking the gear icon next to the custom column step. Use the M function Number.ToText to convert the numeric airport key column to text. This function automatically converts the data type, ensuring compatibility with the region column.

Using M Functions for Dynamic Data Type Changes

In addition to manually specifying the data type conversion, Angelica introduces the function Text.From. This function automatically adjusts the data type of the column based on its current type, providing an easier, error-free way to handle transformations.

• Text.From Function:

  Instead of hardcoding the data type (like Number.ToText), you can use Text.From to dynamically convert the column data type. This ensures that the column adapts to its current state without manually specifying whether it’s a number or text.

Benefits of Using M to Transform Data

Using M functions like Number.ToText and Text.From offers several advantages:

• Minimize Steps: By combining operations in one step, you can reduce the number of transformations in the Applied Steps pane. This makes it easier to manage and troubleshoot your data model.
• Simplify Code: With functions like Text.From, you don’t have to worry about the column's data type in advance. The function automatically adjusts based on the column's existing type, saving you time and effort.
• Cleaner Power Query Editor: Fewer steps in your query mean less clutter in the Applied Steps pane, making it easier to follow your transformations.

Final Thoughts

As Angelica demonstrates in this tutorial, using the M language to change the data type of a custom column in Power BI is a straightforward process. By leveraging the right M functions, such as Number.ToText and Text.From, you can perform powerful data transformations while keeping your Power Query clean and efficient. Whether you’re new to Power BI or looking to refine your skills, mastering these techniques will make your data modeling process smoother and more effective.

For more Power BI tutorials and to explore Pragmatic Works’ full range of on-demand learning courses on topics like Power BI, DAX, Azure, and Power Apps, check out the link in the description below.

Thank you for watching, and be sure to subscribe to Pragmatic Works’ YouTube channel for more insightful tutorials!

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power BI and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.