This tutorial will talk about Power Query data types and connectors. You’ll learn what data types are and see how they are compatible with each other. You’ll also learn about operators in the Power Query M Language. These will help you understand how your queries generate results and answers.
The Data Types In Power Query
The M language has a small set of built-in types that can be split into two main groups: Primitive Types and Structure Types.
The most common Primitive Types that you see in data reports are the following:
Any is a type that classifies all values. It means that every value is a subtype of any. You’ll also see complex types like time and date in the third row of the image.
On the other hand, these are the most common Structure Types:
It is also possible to ascribe a type.
Ascribed Types are not formats applied on top of a Primitive Type because there’s no such thing as formatting in Power Query. Formatting is something only you can do in an application that receives data from Power Query, like Excel or Power BI.
You need to be aware of the distinction between data types and formats because they don’t refer to the same thing. A format controls how a number is displayed without affecting the underlying value, while a data type changes the precision of a value to be consistent with the type that’s been described.
The M engine doesn’t do any type-checking at runtime. So if a column is a number type and you tell the engine that it is a text type, it won’t give you issues. But if you call that column in a function that requires a number type, things will start to fall over. That’s because there’s no automatic type conversion in M.
Compatilibity Of Power Query Data Types
Type compatibility also exists between data types. There’s a difference between a value’s type and its compatibility with another value type.
Compatibility checks are performed at the nullable primitive type level. An M is compatible with another M type if and only if all values that conform to the first type also conform to the second type. If that’s not the case, a type mismatch error will be raised.
To better illustrate that, here is an overview of the Data Type Conversion Matrix.
Expressions And Operators
The green and red circles can speak for themselves. On the other hand, the blue circles mean that the conversion will add values to the original value, while the orange circles mean that it truncates the original value.
The Power Query M formal language includes a set of operators that can be used in an expression. Operators are applied to operands to form expressions. The meaning of an operator can vary depending on the operand value type.
Here are some sample expressions:
In the first expression, the numbers 1 and 2 are operands and the plus or addition sign is the operator. This expression generates a numeric value of 3. However, you can see in the second and third expressions that adding a text value to a numeric value or adding two text values is not supported.
This is one of the distinct differences between Excel, DAX and M. Excel and DAX formulas perform automatic type conversion while the M engine doesn’t. If you use an ampersand (&) instead of the plus sign, the two values will be combined.
Ampersand (&) is an operator that will result in a concatenation of two text strings like the fourth expression in the image above. It also illustrates how the meaning of an operator can vary depending on the operand value type. This is because it also allows combination of lists and merging of records.
Type mismatch errors are something that you are likely to encounter. So when there’s an issue with your data, it means that you’re not declaring data types correctly. These errors also pop up a lot when you modify or write M codes.
***** Related Links *****
Power Query/M Masterclass #1 – New Course On Enterprise DNA
M Code In Power Query Custom Columns | Power BI
Power Query Editor: Staging Queries
Conclusion
The most important preparation in creating data reports with Power Query is to understand how things work. This will help you write M codes properly and set correct data types in order to build a proper data-generating report in Power BI. Mastering the basics will give you great advantage and improve your data development skills.
Melissa
