Extract Function Metadata in Power Query

Recently, I wrote an article about how you can mix-and-match functions and lists to perform many true/false checks at once. From that article, I provided this example:

It worked ok, still not entirely sold on the utility. But one thing bothered me a little: Why do I need to write the rule if the description in the function metadata provides me enough information?

Turns out I did not need to!

The #shared Keyword

This is an essential keyword to know for Power Query. Anytime you want to see the functions defined in the Power BI/Excel file that you are working on, all you have to remember is the #shared keyword. All you have to do is define it in a step and you get a list of records. You can convert that to a table and find documentation within the app.

If you want a more useful table quickly, a little bit of M goes a long way:

let
    Source = 
        Table.SelectColumns(
            Table.AddColumn(
                Table.AddColumn(
                    Table.AddColumn(
                        Table.SelectRows(
                            Record.ToTable(#shared), 
                            each Text.StartsWith(_[Name], "Text.")
                        ), 
                    "Description", 
                    each Value.Metadata(Value.Type([Value]))[Documentation.LongDescription]
                    , type text
                    ), 
                    "Code",
                    each Value.Metadata(Value.Type([Value]))[Documentation.Examples]{0}[Code]
                    , type text
                ),
                    "Result", 
                    each Value.Metadata(Value.Type([Value]))[Documentation.Examples]{0}[Result]
                    , type text
                ), 
                {"Name", "Description", "Code", "Result"}
            ),
    Remove_Errors = Table.RemoveRowsWithErrors(Source, {"Name", "Description", "Code", "Result"})
in
    Remove_Errors

This code extracts all of the Text functions and metadata from the functions themselves. Note: Text.Type returned an error, but I did not notice anything else doing so.

The code above returns this:

So, if I need to get a quick view of the Text functions, this is a simple way to do so.

Using Metadata in Functions

There are a lot of articles out there on how to use metadata to improve documentation of your code. Read them all, it is a great skill to develop and makes things easy to use. Your future self will thank your present self for doing it.

Now, I am going to present a reworked function of the one at the beginning of this post. Here it is:

(str as text, starts_with as text, ends_with as text) =>
  let
    fnGetFunctionMetadata = (function_name as text, return as text) =>
      let
        metadata = Value.Metadata(
          Value.Type(
            Table.SelectRows(Record.ToTable(#shared), each _[Name] = function_name)[Value]{0}
          )
        ), 
        result = 
          if Text.Lower(return) = "name" then
            metadata[Documentation.Name]
          else if Text.Lower(return) = "description" then
            metadata[Documentation.Description]
          else if Text.Lower(return) = "example" then
            metadata[Documentation.Examples]{0}
          else
            "Invalid return parameter value provided"
      in
        result, 
    Source = List.Transform(
      {
        (x) => [
          check       = Text.StartsWith(x, starts_with), 
          name        = fnGetFunctionMetadata("Text.StartsWith", "name"), 
          description = fnGetFunctionMetadata("Text.StartsWith", "description")
        ], 
        (x) => [
          check       = Text.EndsWith(x, ends_with), 
          name        = fnGetFunctionMetadata("Text.EndsWith", "name"), 
          description = fnGetFunctionMetadata("Text.EndsWith", "description")
        ]
      }, 
      each _(str)
    )
  in
    Source

Up front is a nested function called fnGetFunctionMetadata. It takes a function name and a return type as parameters. Afterwards, I repeated the framework of the prior function, but call fnGetFunctionMetadata to pull out the name and description of the check I am performing.

Calling the function returns records that look something like this:

Not bad. Makes the multi-check framework easier to build and maintain.

The function obviously works on its own as well:

(function_name as text, return as text) =>
  let
    metadata = Value.Metadata(
      Value.Type(Table.SelectRows(Record.ToTable(#shared), each _[Name] = function_name)[Value]{0})
    ), 
    result = 
      if Text.Lower(return) = "name" then
        metadata[Documentation.Name]
      else if Text.Lower(return) = "description" then
        metadata[Documentation.Description]
      else if Text.Lower(return) = "example" then
        metadata[Documentation.Examples]{0}
      else
        "Invalid return parameter value provided"
  in
    result

Takeaways

Using metadata effectively, either by documenting your work or extracting information can be super helpful. Also, records continue to be a great way to extract and return related information in one go.