Tag Archives: Microsoft Excel

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 →

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 →

The Flair of a Culture (or Two)

• , , ,

Numbers and dates are formatted differently in different parts of the world. How are these cultural differences handled in the realm of Power Query? Turns out, arguably, there can be not just one—but two—sets of rules in play.

In the M language, numbers and date/time-based values are natively stored in culture-agnostic formats. It doesn’t matter what part of the world you’re in or how it formats values, when #date(2023, 1, 23) is evaluated, Power Query understands that the referenced year is 2023, the month is 1 and the day is the 23rd—and it maintains this understanding throughout the value’s lifetime. Similar holds true with the other date/time types, as well as with numbers.

On the other hand, when converting values of these types to or from text, culture does come into play—but which culture?

Number.ToText(123456.78, "n")
// outputs:
// 123,456.78 (if the culture is en-US)
// 123.456,78 (if the culture is es-ES)
// 123 456,78 (if the culture is se-SE)
Continue reading →

The Oldest Is Not Always the Minimum: Power Query Date-Based Values May Not Compare the Way You’d Expect

• , , ,

The task seemed simple enough: Determine the earliest date between two columns, so that this value could be used when generating a date dimension.

A little M code that took the minimum of each column, then the minimum of those minimums seemed to meet the need until—ouch—I discovered it wasn’t finding the earliest date. Why?

let
  OrderCreatedMin = List.Min(SomeTable[OrderCreated]),
  TransactionReceivedMin = List.Min(SomeTable[TransactionReceived]),
  OverallMin = Date.From(List.Min({ OrderCreatedMin, TransactionReceivedMin }))
in
  OverallMin

What do you think? Do you see any problems with the above?

Continue reading →

M Language Specification in Review: 2022

• , , ,

During 2022, the Power Query M Language Specification received seven substantiative revisions (beyond typo fixes, formatting tweaks, and the such). Each brought clarification to ambiguous points or corrected cases where the specification did not align with actual mashup engine behavior. None of the revisions added new language functionality or otherwise resulted in the mashup engine changing.

Interestingly, while the M language gained two new features last year (try‘s catch and structured error messages), neither of these has yet to make it into the language spec.

Continue reading →

Power Query M Primer (Part 25): Extending the Global Environment

• , ,

To the average Power Query user, how the standard library and data connectors end up in the global environment may be irrelevant. What matters is that, however they get there, they’re there and they work! But in the world of advanced M development, how identifiers come to be injected directly into the global environment becomes interesting. Of particular pertinence is the extension/module system that plays a pivotal role in part of this process.

Welcome to a new world: extending the global environment, here we come!

Continue reading →