Tag Archives: Types

Value.ReplaceType & Table Column Renames (Bug Warning!)

, , , , ,

If you work in the advanced realm of ascribing types, there are a couple interesting behaviors to be aware of related to table column renames (including a bug!).

Positional, Not Name-Based

Imagine you want to set column type claims on a table, so you create a query that uses Value.ReplaceType to ascribe an appropriate new table type.

// BaseDataSet
let 
  // in real life, comes from a database
  Data = #table(
          {"Amount", "TransactionID"}, 
          {
              {100.25, 1},
              {32.99, 2}
          }
        )
in
        Data

// MyNicerTable
let
  Source = BaseDataSet, 
  Result = Value.ReplaceType(Source, type table [Amount = Currency.Type, TransactionID = Int64.Type])
in
  Result
Query output:
| Amount | TransactionID |
| 100.25 | 1 |
| 32.99 | 2 |

So far, so good.

Later on, someone decides that the ID column should be moved to be leftmost, so they reorder columns by editing BaseDataSet. However, they don’t touch your MyNicerTable query with its Value.ReplaceType code. Look closely at what that expression now outputs:

Query output showing column names/types swapped

The column that contains transaction IDs is now named “Amount” and typed Currency.Type. Similarly, “Amount” values now show up under the column name “TransactionID” which is typed as whole number. Ouch!

Continue reading

Always Identical Outputs? Switch Statement vs. Switch Expression

, ,

Are these two statements identical in what they return?

public object Switcher(bool flag) 
{
  switch (flag) {
    case true:
      return 1;
    case false:
     return 10.5;
  }
}
public object Switcher(bool flag) 
{
  return flag switch {
    true => 1,
    false => 10.5
  };
}

No.

TestResult –
Switch Statement
Result –
Switch Expression
Assert.Equal(1, x.Switcher(true));PassFail – Error Message:
Assert.Equal() Failure
Expected: 1 (System.Int32)
Actual: 1 (System.Double)
Assert.Equal(10.5, x.Switcher(false));PassPass

Scratching your head trying to find the difference? The logic in both looks identical. Arguably, it is.

Continue reading

Power Query M Primer (Part 19): Type System IV – Ascription, Conformance and Equality’s “Strange” Behaviors

, , , ,

At first glance, it seems logical to expect the mashup engine to error if you attempt to ascribe a seemingly incompatible type to a value. Surprisingly, this doesn’t always occur.

During ascription, the engine validates that the type’s base type matches the value’s base type (so, for example, you can’t ascribe type logical onto a date value because the two base types are different). However, for types with children, those child types (list’s item type, record’s field types, table’s column types and function’s argument and return type assertions) have no effect on mashup engine behavior and are not validated for compatibility. What?!

For example, you can ascribe an item type of text onto a list of numbers, and the mashup engine won’t complain. Ascribe a column type of logical onto a column containing dates and M will comply. Similar holds true for records: a field type of duration can be ascribed onto a field containing a function and no error is raised.

Value.ReplaceType({1, 2, 3 }, type { text })
Value.ReplaceType(#table({"Col1"}, {{ #date(2020, 6, 24)}}), type table [Col1 = logical])
Value.ReplaceType([FieldA = () => ...], type [FieldA = duration])

For functions, the newly ascribed type’s argument and return type assertions aren’t validated or enforced; instead, the assertions specified when the function was originally defined continue to be what the engine follows. Take a function argument originally defined as number and ascribe as text to it. Despite the ascription, the mashup engine will expect the argument’s value to be compatible with number, not text, when the function is invoked.

let
  Func = (input as number) as number => input,
  NewType = type function (input as text) as text,
  Ascribed = Value.ReplaceType(Func, NewType)
in
  Ascribed("hi") // errors
  // Ascribed(1) // works fine  

These behaviors seem strange—and they aren’t the only strangeness related to Power Query’s types. Comparing type values may also not work the way you expect. Think TypeValueA = TypeValueB will return true if the two types are identical? Maybe. Maybe not!

Fasten your seat belt. We’ll try to define and then clear up a bit of this confusion. It will be a journey! Here we go….

Continue reading

Power Query M Primer (Part 18): Type System III – Custom Types

, , , ,

After gaining a solid grasp on type system basics and exploring type facets, it’s now time to learn about complex types (also known as custom types or derived types). Thankfully, constructing these types isn’t very complex. Don’t worry, though, we’ll make up for that simplicity when we talk about how M handles them!

Let’s dive right in. To keep things simple, the focus for this post is syntax and conformance rules. We’ll save most of the discussion about how M works with these types for the next post in this series.

“Conformance—that sounds complicated!” you might be thinking. Actually, no. Just the name makes it sound that way. Saying that a value conforms to a type means that the value can be described by the given type, or to put it in other words, the value is compatible with the type. So, the numeric value 1 conforms to types number, nullable number, anynonnull and any because each of those types can be used to describe that value. Conformance rules, simply put, are the rules used to determine whether a value conforms to—is described by—a type.

Now, on to the custom types!

Continue reading

Power Query M Primer (Part 17): Type System II – Facets

, , , ,
Snip of 'Column Type' menu showing 'Decimal Number', 'Currency', 'Whole Number' and 'Percentage'

In query editor, ever notice that the column data type menu includes four options for numbers: Decimal Number, Currency, Whole Number and Percentage? In this series, we’ve only talked about one numeric type: type number. Are there types we’ve missed?

Behind the scenes, menu item Decimal Number maps to type number, Currency to Currency.Type, Whole Number to Int64.Type and Percentage to Percentage.Type. If you look at the names defined in your Power Query environment, you’ll likely see a host of other “Type” names, including Int8.Type, Int16.Type, Int32.Type, Single.Type and Double.Type. What are all these “Type” names—even more types we have yet to cover?!

Nope! Introducing type facets.

Continue reading

Power Query M Primer (Part 16): Type System I – Basics

, , , ,

Power Query’s type system helps us understand the values we works with, allows us to describe the kinds of data expected by functions we write, offers a means to share documentation (which IntelliSense can display) and provides a mechanism to inform consumers about the structure of the data we’re handing them (which the host environment can use—for example, guiding Power BI to correctly set column types).

To sum up M’s approach to types at a high level:

Every value has a type. A type is itself a value. Types are used to classify values.

A simple statement, but with a lot packed into it and behind it—so much, in fact, that we’ll explore Power Query’s type system in over several posts. Today, we’ll start with the basics, centered around what are known as nullable primitive types. Later, as we get deeper in, hold on to your hat—you might find a major puzzling surprise, where the type system doesn’t work the way you’d expect.

Let’s start delving into the type system by examining the summary statement we read a moment ago.

Continue reading

Power Query M Primer (Part 10): Types—List, Record

, , , ,

You might be inclined to skip learning about list and record and jump directly to table. After all, as far as types that hold other values go, you mostly work with table. A table’s data is made up of simple values, so it makes since that you learned about them (parts 6, 7, 8 and 9). However, list and record might feel like types you won’t normally use, so why not skip them?

Turns out, a table behaves in ways like both a list and a record. If you want to leverage all that table offers, learning about these types is foundational. Besides, as you work with M, you just might find that list and record are more useful in their own right than you first thought.

Oh, did I mention that there’s a surprising relationship between let expressions and type record? All the more reason not to skip over it!

Continue reading

Power Query M Primer (Part 8): Types—The Temporal Family

, , , ,

Temporal—something related to time (not to be confused with the word temporary). M offers several temporal types: date, time, datetime, datetimezone and duration.

This group of types has a lot in common, so we’ll explore it a little differently than the types we’ve encountered previously. First, we’ll introduce each type and look at its unique facets. Then, we’ll examine how the various types in this family play together (like common functionality they share).

Continue reading

Power Query M Primer (Part 7): Types—Numbers

, , , ,

In our exploration of Power Query’s types, number is next!

You might think that working with numbers would be so simple we’d hardly need to talk about them. However, there’s a got-ya that can bite: if you’re not careful, you can end up with arithmetic not producing the results you expect! After we go over M’s syntax for numeric literals, we’ll talk about this potential pain-point and how to not let it cause unexpected complications.

Also, in M, columns can be tagged to identify the specific kind of numbers they contain. Properly setting this subtype can improve performance and storage as well as enhance the default formatting used for the column’s values. We’ll learn how to do this (it’s easy!).

Continue reading