Avoid Changed Type steps when Adding Columns in Power Query
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.
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.
Any GUI-created queries from “Add Columns” that do not specify a data type, for example:
- Custom Column
- Conditional Column
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 3rd argument to Table.AddColumn:
- type number
- type date
- type time
- type datetime
- type datetimezone
- type duration
- For steps that use Table.AddColumn, write the 3rd 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!Follow @dax_services