Hi Power BI Folks! Welcome to our very first blog post here at daxproservices.com. This will be the start of a great journey of Power Platform posts together.

I just wanted to share a tip that will help you save some steps while writing your Power Query queries, especially if you are not familiar with M formulas and the Advanced Editor.

Goal

We are going to save some steps in a typical PQ query by modifying Table.AddColumns to specify the data type right, thus, avoiding creating “Changed Type” steps.

Applies to

Any GUI-created queries from “Add Columns” that do not specify a data type, for example:

• Custom Column
• Conditional Column

Example

In this example, I added a Conditional Column to a query with the GUI:

As you might notice, the added column shows “ABC 123” in the data type icon. This means it has no actual data type, also known as “type any”.

The usual way

To change this to text, we can proceed to click the data type icon to change this to Text, which will create a new “Changed Type” step:

A better way

It turns that this Table.AddColumn function, well documented here, can take a third argument which you can easily write by yourself by editing the step formula to specify the column type.

Simply edit the formula of the “Added Conditional Column” step by typing

, type text

just before the closing parenthesis:

With this slight modification, we are executing two steps at once:

• Adding the column
• Assigning a data type to it

No need for further “Changed Type” steps!

Now, just delete the “Changed Type” step, because it’s not needed anymore:

Other common data types

These are some other data types that you might commonly write as 3^rd argument to Table.AddColumn:

• type number
• Int64.Type
• Currency.Type
• type date
• type time
• type datetime
• type datetimezone
• type duration

Summary

• For steps that use Table.AddColumn, write the 3^rd argument that takes the data type
• This way, there is no need to create a further “Changed Type” step

I hope you liked this first blog post, not too long so we can break the ice! Feel free to share your thoughts with us.

Follow us on Twitter to Keep in touch for more to come!