You probably know that you can manually set data type for custom column created in Power Query with help of Table.AddColumn function (using 4th argument).
Rick de Groot has recently published a good post about this at Gorilla.BI.
Just be sure you use right data type for the result of your calculation.
Once I faced strange Power Query behavior, as I thought, but later I understood that everything works fine (probably).
Experiment with Duration
Assume we want to have a column with a difference between column [Date] and Today. Something like this
We can manually add Int64.Type as a desired resulting type to the 4th argument of Table.AddColumn
Icon in the column header has been changed to Whole Number, but values in the column are still shown in Duration type (result of Date – Date, according to documentation on M language).
Hence, no data type conversion happened for values.
If we try to load data in Power BI Desktop we will see following picture
As you may see – column “Custom” is empty because all values do not correspond to the column’s data type.
Nevertheless, it works fine when we add ColumnType conversion as a separate step
Data looks like Whole Number in the preview.
And PBD loads data without error message
Experiment with mixed column
Using “Enter Data” I created a table (“Enter Data” always returns text type)
Then I added another column and manually added type conversion argument.
Column data type icon has been changed but no errors shown in the column, which means that 4th argument of Table.AddColumn doesn’t trigger type conversion!
It only changes record about column data type, but doesn’t check if values in the column correspond to that type.
However, Table.TransformColumnTypes (typical #”Changed Type” step) performs two actions
- Changes info about column data type
- Converts values to the desired type
When no conversion possible it returns Error
Conclusion
4th argument of Table.AddColumn is a very handy option if you want to reduce number of query steps. You just need to keep in mind that it doesn’t convert values to the resulting type. If value cannot be converted to the column type you will get an error during load to the data model, and column will contain blank values.
What to do if you don’t want to use Table.TransformColumnTypes as a separate step?
As 4th argument doesn’t trigger values conversion, you need to make sure that the resulting values will be of the required type.
Try to protect your query from possible errors by using following pattern and one of the *.From functions: Number.From, Date.From, Int64.From, Decimal.From etc.
try requiredtype.From( expression ) otherwise expressionUsing this pattern you can control what to return if value cannot be converted.
Happy converting!
PBIX sample can be downloaded from this link.
UPD: Read more about data type conversion in Miguel Escobar’s post.