Power Query M Primer (Part 20): Metadata

Power Query allows information about a value to be attached to a value.

Ordinarily, these descriptive tidbits are invisible. Their presence does not change the mashup engine’s behavior. However, code (yours or others’, such as code in the standard library or in the host application) can intentionally choose to read and set these annotations, known as metadata, using them as a way to communicate extra information relevant to a particular value. These details can simply be for informational or diagnostic purposes; they can also be factored in by expressions as they determine how to behave.

Take parameters as an example. Parameters get special treatment in Microsoft Power BI’s user interface, yet in Power Query itself, they are stored as ordinary values.

This is good. You shouldn’t need to do anything special to consume a parameter; it should just be a value to your code. Yet parameters are special, but where is that “specialness” (like their configuration settings) hiding? From Power Query’s perspective, the answer is—yes, you guessed it—in metadata.

Series Index

• Introduction, Simple Expressions & let (part 1)
• Functions: Defining (part 2)
• Functions: Function Values, Passing, Returning, Defining Inline, Recursion (part 3)
• Variables & Identifiers (part 4)
• Paradigm (part 5)
• Types—Intro & Text (Strings) (part 6)
• Types—Numbers (part 7)
• Types—The Temporal Family (part 8)
• Types—Logical, Null, Binary (part 9)
• Types—List, Record (part 10)
• Tables—Syntax (part 11)
• Tables—Table Think I (part 12)
• Tables—Table Think II (part 13)
• Control Structure (part 14)
• Error Handling (part 15)
• Type System I – Basics (part 16)
• Type System II – Facets (part 17)
• Type System III – Custom Types (part 18)
• Type System IV – Ascription, Conformance and Equality’s “Strange” Behaviors (part 19)
• Metadata (part 20) [this post]
• Identifier Scope & Sections (part 21)
• Identifier Scope II – Controlling the Global Environment, Closures (part 22)
• Query Folding I (part 23)
• Query Folding II (part 24)
• Extending the Global Environment (part 25)
• More to come!

Metadata

Let’s start with the basics….

All values have metadata. A value’s metadata is stored in a record—nothing fancy, just an ordinary record. By default, a value’s metadata record is empty.

View

To view a value’s metadata, use Value.Metadata. Below outputs the metadata associated with the value stored in variable SomeValue. In this case, it’s the default metadata value: an empty record.

let
  SomeValue = 1
in
  Value.Metadata(SomeValue) // returns an empty record: []

Replace (Set)

This unexciting empty record can be replaced with a more interesting record using Value.ReplaceMetadata:

let
  SomeValue = 1,
  MetadataReplaced = Value.ReplaceMetadata(SomeValue, [Exciting = true])
in
  Value.Metadata(MetadataReplaced) // [Exciting = true]

(Technically, as we know, values in M are immutable, so Value.ReplaceMetadata really doesn’t modify the current value to update its metadata. Instead, it returns a new value that is the same value as before, just with the previous metadata removed and the specified record set as the value’s metadata.)

Important: Metadata is attached to a value, not a variable. Below, MetadataReplaced (from above) is assigned to YetAnotherVariable. Value.Metadata(YetAnotherVariable) returns the exact same metadata as Value.Metadata(MetadataReplaced) because both variables reference the same value, and metadata is on the value, not the variable.

let
  SomeValue = 1,
  MetadataReplaced = Value.ReplaceMetadata(SomeValue, [Exciting = true]),
  YetAnotherVariable = MetadataReplaced
in
  Value.Metadata(YetAnotherVariable) // [Exciting = true]
  //Value.Metadata(MetadataReplaced) // also returns [Exciting = true]

Merge

Instead of replacing a value’s metadata with an entirely different record, you might want to merge a new value (or values) in with the existing metadata.

You could pull this off by getting the value’s current metadata record, applying a merge between that record and the metadata you want to merge in, then passing the merge’s output to Value.ReplaceMetadata:

let
  Initial = Value.ReplaceMetadata(1, [Exciting = true]),
  Final = Value.ReplaceMetadata(Initial, Value.Metadata(Initial) & [Important = true])
in
  Value.Metadata(Final) // [Exciting = true, Important = true]

A lot is packed into line 3 above: reading metadata, merging a record and setting metadata. The same effect can be achieved with much simpler syntax, thanks to Power Query’s meta operator:

let
  Initial = Value.ReplaceMetadata(1, [Exciting = true]),
  Final = Initial meta [Important = true]
in
  Value.Metadata(Final) // [Exciting = true, Important = true]

Remember: When two records are merged, if the same field name is present in both records, the value for that field from the record that is being merged in overrides the value from the original record. Merging [A = 1] and [A = 11] outputs [A = 11] because the value for A from the merged in record overrides the A value from the original.

Operator meta is effectively doing a record merge, so the same behavior holds true with it. Below, Important is present in both Initial‘s metadata and in the new record provided to Final‘s meta operator. The value for that field in the latter record overrides the value specified in Initial. So, the merge switches Important from true to false.

let
  Initial = Value.ReplaceMetadata(1, [Exciting = true, Important = true]),
  Final = Initial meta [Important = false]
in
  Value.Metadata(Final) // [Exciting = true, Important = false]

Remove

We’ve viewed metadata, set (replaced) metadata and merged metadata. What if you want to remove metadata? Well, since all records have metadata, you technically can’t get rid of it altogether; instead, in the Power Query world, removing metadata equates to replacing it with an empty record. You could do this manually, using Value.ReplaceMetadata(SomeValue, [])—or, better yet, you could use Value.RemoveMetadata, which achieves the same effect using a method made just for that purpose:

let
  Initial = Value.ReplaceMetadata(1, [Exciting = true]),
  Final = Value.RemoveMetadata(Initial)
in
  Value.Metadata(Final ) // returns an empty record: []

Metadata’s Lifespan

If a value with metadata is passed as an argument to an operator, does the output of the operation have the original value’s metadata associated with it? Say you add two values that each have metadata, or a value with metadata and one that doesn’t. Does the resulting sum have any of that metadata associated with it?

No. The operator’s output is a different value: it may be derived from its inputs, but it is not any of those original values. Even if the output happens to be the same as one of the input’s values, conceptually it still represents something different—a new value that is the addition operator’s output. Since it’s a new value, it starts off with a clean metadata slate.

M’s operators, excepting operator meta (of course!), do not carry metadata from their inputs to their outputs. The values they output will always have their metadata set to the default of a blank record.

Below, Result won’t have Original‘s Important = true metadata because it is a new value.

let
  Original = 1 meta [Important = true],
  Result = Original * .5
in
  Value.Metadata(Result) // returns an empty record: []

If you want input metadata added to the output from an operator, you’ll need to write logic to make this happen. This makes sense. Take an example like the above—how is the mashup engine supposed to know whether an input being Important makes the multiplication operator’s output Important? It doesn’t. It’s up to you to set metadata on the output, based on your rules, when its presence is appropriate.

There you have it: the technical details of metadata, at the language level. A simple concept.

Built-In Special Metadata?

You may be waiting for a list of special metadata field names that are built into the language that make special things happen—like if you set a value’s metadata to SecretCode = 123 then evaluate the expression, the mashup engine makes tea for you, or mows your lawn—or at least executes twice as fast or something like that. There aren’t any at the language level. The language simply provides support for metadata but gives no special meaning to it.

This means that Power Query itself does not change behavior based on the presence or absence of certain metadata. Any special metadata behaviors come from logic leveraging the metadata capabilities offered by the language, not from the language itself. This includes logic in the host environment (Microsoft Power BI, Microsoft Excel, etc.) and in the standard library, as well as in mashup expressions you write.

Power BI Parameters

For an example, take this article’s introductory example: Power BI (a host application) uses metadata to configure parameters. To Power Query (the language, including the mashup engine), these metadata elements are just arbitrary metadata. Their special meaning comes only because the host application gives them that meaning.

In Power BI Desktop, create a parameter named SomeParam and set to a scalar value, say, 10. Now, view the metadata on it.

Value.Metadata(SomeParam) 
// returns:
// [
//    IsParameterQuery=true, 
//    Type="Number", 
//    IsParameterQueryRequired=true
// ]

Wow! Notice how Power BI saved metadata on the value that describe its parameter settings. To the mashup engine, this metadata is opaque: fields and values with no intrinsic meaning that it has been asked to hold. To Power BI, these details tell it that this value is a parameter and tell it how that parameter is configured.

Let’s explore parameter metadata a bit more.

In Query Editor, select SomeParam then open Advanced Editor. You can now see where the parameter’s metadata is defined. (You can even manually edit it, if desired.)

10 meta [IsParameterQuery=true, Type="Number", IsParameterQueryRequired=true]

Now try this: After copying the meta [...] bit to the clipboard, remove it from the expression then close Advanced Editor. Notice how SomeParam no longer behaves or looks like a parameter in the UI (for example, the parameter icon is no longer displayed next to its name). It isn’t a parameter anymore because the metadata that instructed Power BI to treat it as one is gone. It’s still a value, just not a value that’s treated as a parameter by the host application.

Now, create a new expression named SomeValue, and set it to a scalar value, say 10. Open Advanced Editor and paste in the meta [...] bit you copied just a moment ago. Close the dialog and notice how Query Editor’s UI now treats SomeValue as a parameter! Keep in mind that parameters are strictly a host application concept—to Power Query, SomeValue is still an ordinary value, just with some opaque metadata.

Playing with parameter metadata may be a fun diversion. While interesting, it’s likely not particularly useful. Most folks are going to keep using the UI to create parameters (instead of hand-crafting them in code) and there probably are few practical uses you’ll find for programmatically reading parameter metadata. In fact, even if you found one, you’d need to be careful about relying on it, as parameter metadata is an internal implementation detail of Power BI, so its structure could change in the future without notice. But now, at least, you’ve seen a real-world example of where and how metadata is used.

Function Documentation

How about a practical use for metadata? Ever notice the difference in the documentation Query Editor displays between your functions and standard library functions?

Let’s take a look. Define a function, then reference the function’s name (don’t invoke the new function, just reference it—i.e. leave off the parenthesis part).

You might define your function (named, say, SomeFunction) something like:

let
  DoSomething = (input as number) as number => input * 10
in
  DoSomething

Then, in a new query, reference it without invoking it (notice that no “invoke function” parenthesis are present):

SomeFunction

Query Editor displays the function’s signature and a simple UI allowing you to specify a parameter value and invoke the function. All in all, short and simple.

To contrast, reference a function from the standard library, say Number.Sqrt:

Number.Sqrt

Whoa! What a difference! A description of the function is displayed. Also, several usage examples are given.

How did Microsoft pull that off? Metadata—function documentation is defined using metadata, which means you can use it to document your functions, too!

“Wait!” you say, “you told me before that metadata has no special meaning to Power Query. Now you’re saying that it does—that it’s the Power Query way to document functions.”

No, what was said before still holds true. At the language level—to the mashup engine–the documentation metadata items we’re about to cover are just opaque metadata values with no intrinsic meaning. It’s Query Editor (a host application) that gives them their meaning. There’s a set of documented metadata field names which, if present on a function’s type, will be used by Query Editor to influence what its UI displays.

What we’re about to explore is also one of the main motivations for manually defining and ascribing custom types (parts 18 and 19 of this series)!

Let’s define and ascribe a type to the function we created a few moments ago. This type should match the function’s type (have the same function signature). While we’re at it, we’ll associate a few metadata fields with the new type.

let
  DoSomething = (input as number) as number => input * 10,
  Type = type function (input as number) as number 
    meta [
      Documentation.Name = "SomeFunction", 
      Documentation.LongDescription = "Some very long and elegant description.",
      Documentation.Examples = {
        [Description = "Positive number as input", Code = "SomeFuction(1)", Result = "10"],
        [Description = "Negative number as input", Code = "SomeFuction(-2)", Result = "-20"]
        }
      ],
  RetypedFunction = Value.ReplaceType(DoSomething, Type)
in
  RetypedFunction

Now take a look at how Query Editor displays our function in its UI. Much nicer!

A note about Documentation.Name: This value should probably match the name of the identifier you assign your function when you define it. For example, if you call your function SomeFunction, then when you set Documentation.Name, you should probably set its value to “SomeFunction”, as well.

Why? Consumers of your function may later assign it to other identifiers (like ReallyCoolFunction = SomeFunction). Having Documentation.Name match the name you initially gave it will help them figure out which function they’re really working with. For example, when someone looks at the documentation for ReallyCoolFunction, they’ll be able to see that they’re really working with SomeFunction.

Oh, before we leave Documentation.Name: If you omit it, Query Editor’s UI will ignore the other function-level documentation metadata items (at least, that’s the behavior as of the time of this writing). If, for example, you set just Documentation.LongDescription, Query Editor’s documentation output won’t display it because no Documentation.Name is present.

You might be wondering why we didn’t just add the metadata to the function when we defined it originally. Instead, we first defined the function, then defined a custom type for the function, then ascribed that type to the function. Why not just apply the metadata to the function and skip the extra work?

Why didn’t we do something like:

((input as number) as number => input * 10) meta [ … documentation goes here …]

Well, it wouldn’t have done us any good. Documentation metadata needs to be attached to the function’s type, not the function itself. When we define the function, we can attach metadata to it (as the above example shows), but the language doesn’t provide a way for us to attach metadata to the type generated for the function at the time the function is defined—so, for better or worse, it take a multistep process to attach it.

Parameter Documentation

The above documentation metadata applies at the function level. Query Editor also supports documentation metadata for individual parameters. Below shows a couple of these.

let
  DoSomething = (input as number) as number => input * 10,
  Type = 
    type function (
      input as (type number meta 
        [
          Documentation.FieldCaption = "Number to multiply",
          Documentation.FieldDescription = "Will be multiplied by 10",
          Documentation.SampleValues = { 1, 0, -10 }
        ]
      )
    ) as number,
  RetypedFunction = Value.ReplaceType(DoSomething, Type)
in
  RetypedFunction

Interestingly, in Query Editor, while the field’s caption is displayed, its description is not, and only the first of its sample values appears.

There are several other parameter-level documentation metadata opportunities:

• Documentation.AllowedValues—Does not any way change the values the function accepts; instead, causes the function invocation UI to constrain the input it accepts to a dropdown that’s populated with the AllowedValues.
  
• Documentation.FieldDescription—Interestingly, this field’s value doesn’t seem to show up in Query Editor’s UI.
• Formatting.IsMultiLine—Switches the parameter’s input control to a multi-line textbox.
• Formatting.IsCode—Supposed to cause the parameter’s input control to be formatted like code, but as of this writing, doesn’t seem to affect anything in Query Editor, either.

Record Parameters

For record parameters, the expected “shape” (that is, the required and allowed fields) can be described by giving the parameter an appropriate custom record type in the type that’s ascribed to the function. The metadata elements Documentation.FieldCaption, Documentation.AllowedValues and Documentation.SampleValues can optionally be applied to the record type’s fields to influence how Query Editor displays them in the function invocation UI.

let
  Func = (input as record) => ...,
  NewType = type function (
    input as
      [
        Server = (
          type text meta
          [
             Documentation.SampleValues = { "localhost"}
          ]
        ), 
        optional Timeout = (
          type number meta
          [
            Documentation.FieldCaption = "Time Out [in seconds]",
            Documentation.AllowedValues = { 10, 60, 360 }
          ]
        ),
        optional CutOff = date
      ]
    ) as any,
  Ascribed = Value.ReplaceType(Func, NewType)
in
  Ascribed

(Reference: Describing a Function’s Record Parameters)

Your Logic

What about using metadata from your code? We’ve looked at how Power BI uses it for parameters (interesting, but not so practically useful) and how you can use it to add documentation (setting metadata fields, which Query Editor’s UI looks for). However, both of these rely on the host application to make the special behaviors happen. Are there practical uses for defining and handling “custom” metadata all on your own?

While the sky is the limit, admittedly, custom uses for metadata are likely not every day occurrences. Usually, the values we work with are sufficient by themselves; they don’t need to be accompanied by data about them (that is, metadata). So when might you write custom logic that processes metadata?

Here’s one possibility:

Let’s say you want to pull data from a web API. This particular API endpoint returns at most 100 records per invocation. If the requested data set is larger than 100 items, results are paged, with each page (except the last) accompanied by a link that can be used to get the next page of data:

// JSON response to first request -- returns first 100 rows
{
  "Data": [ …rows 1-100…],
  "Next": "https://somewhere/someApi?nextPageToken=1905617808"
}
	
// JSON response to second request -- returns second 100 rows
{
  "Data": [ …rows 101-200…],
  "Next": "https://somewhere/someApi?nextPageToken=899780178"
}
	
// JSON response to third request -- returns final 19 rows
{
  "Data": [ …rows 201-220…],
  "Next": null
}

You’re thinking to start by writing a function that is passed a URL and uses it to fetch a page of data. Other logic you’ll write will call this function as many times as needed to get all the pages, then stitch them together into a single table.

Your “get page” function needs to return two things: a table of the page’s rows and the next link’s value. Returning a table is simple enough, but how can you accompany it with the next link? Metadata is a great way to do this:

GetPage = (url as text) as table =>
  let 
     Response = Json.Document(Web.Contents(url)),
     Table = Table.FromRecords(Response[Data])
  in
    Table meta [NextLink = Response[Next]]

Nice! Notice how the next link is there for those who need it, but someone who doesn’t care about it doesn’t need to know it exists—they can just treat the function’s output as an ordinary table.

There’s other ways this could have been pulled off, like having the function return a record with a field for the table and one for the next link. Here’s a test: If you’re creating a new data structure (like a record) simply to accompany a value of interest (in this case, a table) with extra information (such as a next link), prefer metadata—it’s the built-in way for communicating data about a value along with the value.

Conclusion

There you have it! Metadata: information about a value that is attached to the value. Now you know what it is and how it works so you can leverage it in those scenarios where it is helpful!

Next time? Let’s talk about identifier resolution and sections.

Until then, happy data crunching!

Revision History

2021-10-11: Added details about defining the expected shape of record parameters.