Understanding Data Types

In the previous chapter, we looked at the role of values in shaping expressions and queries within the M language. We learned how values interact with various operators and functions and their unique characteristics. Building upon this knowledge, let’s continue this journey by turning our attention to data types.

Data types serve as a classification system for values, providing information about their structure and usage within M. Understanding data types is crucial for effectively using M and storing values efficiently. In this chapter, we provide a comprehensive overview of data types, their significance, and their application in practical scenarios.

We will start by examining why data types are important. You’ll discover the various data types in M and the contexts in which they are used. We also explain how to identify and understand different data types, with a focus on type conversion. Additionally, this chapter introduces facets...

What are data types?

The M language has both values and data types. In the official documentation, data types are formally referred to as types because they don’t just classify data but also classify functions and the data type itself. Throughout this book, we use both the terms data types and types so as not to get confused when comparing them with value types. So, what exactly are data types, and how do they compare to values?

The type system

The type system in Power Query helps classify values, offering information about the structure of your data. When creating custom functions, data types specify the required values. Additionally, they convey essential information to any system into which the data is loaded. Let’s start by delving into an example.

Each kind of value in the M language has a data type. It’s a special kind of value that characterizes the kind of value and carries additional metadata that is specific to the shape of the value. This...

Importance of types

The M language is a query language that is dynamically typed. What that means is that you don’t have to declare variables and their (data) types before you can use them. And, as you just learned, Power Query can recognize what kind of data it receives, but not being explicit about your data types is risky in data. When your column is labeled as type any, it signals that it can contain any value. When you then perform an operation that only works on a particular type of value, your operation may result in an error.

In this section, we’re going to explore why it’s better to be clear about your data types. Think of data types like labeling boxes when you move; it requires some attention when storing, but saves a lot of confusion later on. So, why are data types important?

Clarity and consistency

Data types have an important role in improving clarity and ensuring consistency within datasets. They do this by clearly signaling the nature...

Data types available in M

The M language has a range of different (data) types. They provide a way to classify values and sometimes constrain the kind of data that is allowed in a custom function. You could say data types define a value’s shape and indicate the operations that can be performed on it.

The type system in M can be seen as a hierarchy. At the base, all values conform with the any type. From here, we can get down to more specific types.

A layer down in the hierarchy, you can find more specific types. Think of primitive types, as well as more complex constructions like records, lists, and tables. The type system also allows for the definition of custom types, giving users the flexibility to specify custom data structures.

One can also distinguish between values that can and cannot hold null values through nullable types. This distinction is helpful when dealing with data sources where the absence of a value, represented by null, is a possibility and should...

Type detection

Your first interaction with datatypes in Power Query is likely when you import data. Your data often comes in the form of a table, and the columns for the table can have a data type. When using the default settings, Power Query automatically recognizes the data types of any tables you import. Yet, how Power Query detects the data types of the values in your dataset will depend on the data source used and your Power Query settings. There are two available methods:

• Retrieving data types from data sources
• Automatically detecting types

The following sections look at how these two methods work.

Retrieving data types from a data source

When you import data from a structured data source (e.g., SQL, Oracle, Azure Data Lake, OData feed, etc.), Power Query automatically accesses the table schema from these sources. This table schema is only available in structured data sources and defines the structure of tables in a database. It contains information...

Data type conversion

Whether you import or create data yourself, setting the correct data type is essential. Sometimes, you’ll need to change a data type temporarily to make things work—like making sure a piece of data is accepted by a certain function, or when you’re mixing different types of data.

Let’s say you have a number that’s been input as text. To do any math with it, you’ll need to change it from text to a numeric format. Or, if you’re trying to add a date to a text message, you’ll need to switch the date to a text format first. This kind of switching is often needed when your data is stored in tables.

For these situations, the M language has two main ways to convert your values to another type. You could:

• Create a new column and use a conversion function within an expression
• Transform entire columns to another type

We will look at both approaches in the following sections.

Converting...

Facets

So far, we have looked at the base data types in the M language, both the primitive and the custom ones. However, you may have encountered values that look like data types, but with a different notation. For instance, when selecting a new data type, you see the following popup:

Figure 5.32: The drop-down menu for changing column types

Notice how the menu shows four different ways to label number values. Now, suppose we change a column to Decimal Number and a column to a Fixed decimal number. The UI produces the following code:

Table.TransformColumnTypes( Source ,
  { { "Name",  type number   },
    { "Value", Currency.Type } } )

The first type transformation references the familiar type number. However, what exactly is Currency.Type? The answer is this is one of the Type Facets, commonly referred to as facets.

Type Facets provide additional information about your data types. They become useful when Power Query needs to talk to other...

Join our book community on Discord

https://discord.gg/28TbhyuH

Error handling in Power Query's M language involves techniques for managing and resolving errors that may arise during the evaluation of an expression. By utilizing expressions and functions such as 'try', 'otherwise', and 'catch', you can gracefully handle potential errors and effectively control the execution flow. Additionally, the Power Query Editor offers a convenient built-in feature called the 'Applied Steps' pane, which allows you to step through the code, facilitating the identification of issues within your query.This chapter provides an overview of the error handling capabilities and debugging strategies in Power Query. It aims to equip you with the necessary knowledge to effectively address errors and ensure your queries are more future proof. By understanding common errors that can occur in the M language and applying the techniques discussed here, you will establish...

What is an error?

In Power Query's M language, one of the fundamental building blocks is the ‘expression’, which is responsible for producing a value upon evaluation. When an error is encountered, it indicates that the evaluation of the expression failed and could not be completed successfully. Errors can have various causes, such as invalid identifiers or operations, incompatible data types, and more. Understanding, preventing and effectively handling errors is crucial for creating queries that are more robust and reliable.When evaluating an M expression, there are two possible outcomes:

1. The production of a single value.

This indicates that the evaluation process was successful, and the expression was able to generate a result.

let
    result = 1 / 0
in
    result

Division by zero does not raise an error but returns infinity.

1. Raising an error.

When an error is raised, it indicates that the evaluation of an expression failed to produce a value. The error itself...

Error containment

Power Query's M language integrates containment-based programming principles. Let's explore two aspects and discover what that means for the evaluation of expressions:

1. Type inference

This is the ability of a programming language to automatically deduce and make educated guesses about the data types of values based on their context and usage within the code.For example, if a column contains values like 1, 2.5, and 5, Power Query will infer that the data type for that column is numeric. This allows for more flexible and concise code, as you do not have to explicitly declare the data types of every variable or expression, which benefits the data transformation process.However, it's important to note that type inference is not perfect because Power Query only scans the top 200 rows. If this data is somehow different than the data after row 200, a wrong type is inferred. Be aware that an incorrect type won't always produce errors. Sometimes the values...