Introduction
When working in Power BI’s Power Query Editor, you often need to assign proper data types to your columns. For example, imagine a product table with columns Product, Sales, Date, and Sales Condition. One of these columns might have the data type Any (indicating it contains both text and numbers). If you use the usual approach clicking the data type icon or using the ribbon to change it to Text. Power BI will add a new Changed Type step to your query. While this works, each additional step can clutter your Applied Steps pane and potentially impact performance in complex queries.
Set the Data Type in the Formula Bar
- Instead of using the UI to change the column’s type (and generating an extra step), you can specify the data type directly in the M code for an existing step. Here’s how to do it:
- Remove the added step (if you already changed the type via the UI): In the Applied Steps list, click the X next to the Changed Type step that was just created. This reverts the query to the previous step.
- Select the previous step and open the formula bar: Make sure the formula bar is visible (enable it from the View tab if not). You’ll see the M code for the selected step.
- Modify the M code to include the type: Find the part of the formula where the column is defined or its type is being set. Append , type text (for a Text column) to that part of the code.
- Press Enter to apply: After editing the formula, press Enter. The column’s data type is now Text, and no new step was added to the query.
After editing the M code to include the data type, the Power Query Editor applies the change without adding a separate step. In this example, a custom column was added with type text specified (for a text), so there is no additional “Changed Type” step in the Applied Steps pane. Defining the type inline keeps the query steps list streamlined.
By editing the M formula, the column’s type is set within the existing step instead of as a separate step. For instance, if your step was adding a custom column, the formula might look like:
= Table.AddColumn(#"PreviousStep", "NewColumnName", each <logic>, type text)
M Code Syntax for Common Data Types
Power Query’s M language uses specific keywords or type constants for data types. Here are some common ones you can use when editing the formula:
- Text: type text
- Whole Number (Integer): Int64.Type (64-bit integer for whole numbers)
- Decimal Number: type number (or Number.Type, for a floating-point number)
- Date: type date
- True/False (Boolean): type logical
For example, Int64.Type is used for integer columns with no decimals, whereas type number covers decimal or whole numbers . Using the correct type ensures your data is properly recognized in Power BI.
Example: Creating a True/False Column without Extra Steps
Let’s say you want to add a new True/False flag column. You decide that rows meeting a certain condition should be marked as TRUE. One quick way to do this in Power Query is to output a 1 for true and 0 for false in a custom column formula, since Power BI will interpret 1 as TRUE and 0 as FALSE in a logical context.
However, to make this new column a Boolean logical type, you should explicitly define its type in the formula. If you’re unsure what the M syntax is for a Boolean, try this trick:
- First, temporarily change the new column’s type using the UI: click the data type icon (e.g., the “ABC/123” icon) and set it to True/False. This will add a Changed Type step, and in the formula you’ll see the column is being converted with type logical.
- Now remove that Changed Type step (by clicking the X). Go back to your custom column’s step and edit its formula to append , type logical at the end.
- Press Enter to apply. The column will now show values TRUE/FALSE (instead of 1/0) and is recognized as a logical type, all within the single step.
Using type logical in the formula ensures the column is boolean without an extra conversion step. This technique can be used whenever you create custom columns that need a specific data type.
Conclusion
By defining data types directly in the M code, you avoid unnecessary steps and keep your queries tidier. As your Power BI queries grow, eliminating extra steps makes them easier to read and maintain
