Tag Archives: Microsoft Excel

Power Query: Choice Values for Power Apps/Dataverse Picklist and Boolean Columns

• , , , ,

How do you query out the possible label values of the choice columns (a.k.a. picklists or option sets) in a Dataverse/Power Apps table (entity) using Power Query?

As part of building a dimension table, here’s what I came up with to answer this question. In addition to working with picklist columns, with a slight tweak, the below can be used to fetch the textual label values for true/false column choices (e.g. for a given column, maybe true is configured to show as “Yes”, and false as “No”).

Sharing this so it can hopefully help the next person with the same need…and so that I can remember how to get this information if I need it again down the road. 🙂

Continue reading →

Table Interceptors

• , , , , ,

Have a table, but want to change—to override or augment—some of its behaviors? Not a technique normally needed in day-to-day Power Query, but perhaps useful in certain special scenarios.

Imagine that you are pulling a table containing many product rows from a data source. All works well, except for one performance challenge: Counting rows (e.g. Table.RowCount) is slow because the data connector doesn’t query fold the row count. Instead, Power Query is fetching all the rows in the table in order to count them locally, and this takes a while.

You know that there’s an API endpoint, /product/count, that can be called which will immediately return the count of products. If only you could use the existing data connector, which you are otherwise happy with, and just override how row counting is handled for this table so that it uses /product/count….

Continue reading →

Column Types Don’t Matter, or Do They?

• , , , ,
"Change Type" popup menu for a column

Power Query’s column types can be confusing. You can create or ascribe a column with any type—regardless of the kind of values in the column—and Power Query won’t complain, nor will the values in the column be transformed to the specified type. On the other hand, use Power Query’s “change type” functionality and the chosen column type does have an impact, causing values to be converted, as appropriate, and raising validation errors when that can’t be done.

Why does Power Query only sometimes care about column types? How do we make sense out of the seemingly contradictory behavior where types for columns matter when specified in one place (change type) but not in others (creation, ascription)?

Continue reading →

Type Equality

• , , , ,

Type equality is an advanced—and also confusing—Power Query topic. Sometimes, equality comparisons between type values seem to work as intuitively expected; other times, they may not. What’s going on? Is it okay—is it safe—to use the equals operator between type values?

But first: Why the confusion around this subject? The M language specification lays out a brief set of rules for type equality which define the minimum expected behavior. These may then be augmented by additional rules specific to the Power Query implementation you are using. Grasping the full implications of the former isn’t always intuitively obvious and there is almost nothing publicly written documenting the latter—resulting in Power Query type equality being a confusing, little understood advanced niche.

Let’s try to clear things up. Let’s explore the behaviors of Microsoft’s flagship Power Query mashup engine implementation (i.e. the Power Query that ships with Microsoft Power BI and Excel). We’ll examine both behaviors mandated by the M spec (specifically, the relevant subsections of “Types” and “Operators“) and additional ones that are specific to the flagship PQ implementation.

Let’s get started!

Continue reading →

Rounding: Power Query vs. Others

• , , , ,

Did you know that, by default, Power Query may round numbers differently than you learned in grade school? Unlike DAX, Microsoft Excel and Microsoft SQL Server, which use commercial rounding as their main or default approach to rounding, Power Query is different: it defaults to banker’s rounding.

What’s the difference between these two ways to round? What if you don’t want Power Query’s default—can you override it? What if you don’t ever intentionally round numbers, does this topic even matter to you? (Hint: Possibly yes!)

 Let’s explore some details and options.

Continue reading →

Exploring Power Query Buffering: How Table.Buffer and List.Buffer Work

• , , ,

Table.Buffer and List.Buffer buffer data—but how do they work? How deeply do they buffer? How do they handle errors? And, for that matter, when do they populate?

In a nutshell: Table.Buffer creates a stable copy* of a table’s rows. These rows are fetched only once from the source, regardless of how many times they are read from the buffer. Each time the buffer is accessed, the same* rows are returned in the same order. With List.Buffer, the behavior is identical, except it is list items that are buffered instead of table rows. (*But this may not mean what you think, so keep reading.)

The details have some nuances to them. Let’s explore them, as well as what happens when errors are encountered.

Continue reading →

Render Tables, Lists, Records -> Text

• , ,

Power Query’s simple scalar values—like date, number and logical—can easily be converted to strings. Each has a corresponding type-specific “ToText” method (like Date.ToText or Number.ToText). The generic Text.From can also be used.

But what if you want to render a table, list or record textually? There is no built-in way to convert values of these types directly to text.

However, you can convert them to JSON…and then render that JSON as text!

(input as any) as nullable text =>
  if input = null
  then null
  else Text.FromBinary(Json.FromValue(input))

Handy to render out a complex, nested structure so that you can see all of it at once!

Continue reading →

RowExpression.From/ItemExpression.From

• , , , ,

Power Query’s RowExpression.From/ItemExpression.From function (both names reference the same underlying function) provides a way to learn about what a single-parameter function does by outputting an abstract syntax tree (AST) describing it.

Why might you want to use a programmatic structure like an AST to analyze the logic of a function’s body instead of simply invoking the function?

Well, one reason may be that you are implementing query folding in a custom connector. You might want to translate the filter predicate function passed to Table.SelectRows, or the generator function passed to Table.AddColumn, into the upstream data source’s native query/language. In either case, you don’t want to invoke the passed-in function; instead, you want to understand its behavior so that you can factor it in as you build an equivalent native request/query. RowExpression.From/ItemExpression.From is tailored for this purpose.

Unfortunately, this function is little documented—but it is time for that to change!

(Note: For simplicity, the below will refer to this function by the name RowExpression.From. However, ItemExpression.From is an equally valid way to reference the function.)

Continue reading →

Lazy, Streamed, Immutable: Try Building a Table

• , , , , ,

Lazy evaluation, streaming and immutability are key Power Query concepts which must be understood to truly grasp how Power Query “thinks.” Want to test your understanding in these foundational areas—and try to grow it—by tackling an assignment?!

Your Task

Code up a single row table containing the following columns:

  • A column of hard-coded data.
  • A couple columns whose data is fetched from an API.
  • A couple columns whose data is fetched from another API.
Example of expected table

Where:

  • Neither API is called if the table’s rows are simply counted.
  • Only the first API is called if the columns containing data from the second API are removed from the table, and vice versa.
  • Each API is called at most once, even when multiple columns that contain data from that API are output.

Without:

  • Using any standard library table functions to build the table (i.e. no “Table.*” functions may be used).
Continue reading →