20 thoughts on “Power Query M Primer (Part 20): Metadata

  1. hank

    Hi Ben,
    Thank you so much, it is such a pleasure to dig into M with your insights.
    I wonder if you could please elaborate how you are using your GetPage function and its metadata to build the full table

    Reply
  2. Dwaine Wright

    Ben,

    I haven’t finished all 20 post yet, but this series is by far the best written, inciteful, and masterfully crafted blog entries I have discovered on this not-so-straight-forward subject. Cheers to you!

    Dwaine

    Reply
  3. Nicolas

    Hi Ben,

    thanks for this series, it has been very insightful and very helpful for me trying to understand “how Power Query thinks” 🙂

    Currently, I’m trying to “hack” the partially lazy way of PQ.
    Can you tell how metadata is evaluated ? Is it lazy or eager ?

    If a metadata of a variable is never used, is it still being evaluated ?

    Thanks for your help.

    Reply
      1. Nicolas

        For example in this code, does the CallFunction() get called, even if the metadata [ts] of A is never accessed ?

        let
            CallFunction = (x) => x + 3,
            A = "somevalue" meta [ ts = CallFunction(5) ]
        in
            A
        Reply
        1. Ben Gribaudo Post author

          Thanks for the example. I don’t believe so. Record field expressions are evaluated lazily, so ts‘s value shouldn’t be computed unless something needs that value.

          Reply
  4. Jon

    Great Explanation about using metadata within powerquery

    A quick question. Do you know to access the metadata records of a function (either the built-ins or custom ones)?
    I am not sure how the syntax needs to be within the parenthesis of the Value.Metadata() so the it recognizes the input as a function

    Thanks

    Reply
    1. Ben Gribaudo Post author

      Hi Jon,

      I’m guessing you’re trying to get a function’s documentation metadata, which is stored as metadata on the function’s type, not directly as metadata on the function value itself.

      Does the below help?

      Value.Metadata(Value.Type(Table.SelectColumns)) // replace "Table.SelectColumns" with whichever function is of interest
      
      Reply
      1. Jon

        Thanks Ben for your reply.

        Correct by metadata records I refer to the metadata that contains all the function’s documentation available like the records [Documentation.Name] [Documentation.Description] etc.

        Yes your solution works like a charm. Thanks for the help

        Cheers

        Reply
  5. Lutz

    I actually recently came across a use case for parameter meta data. Someone wanted to use “multiple parameters with the same name” for a filter. Turns out they were using the “Suggested values” list option of the parameter, and wanted to use that list for filtering.

    A quick look revealed the meta data of the parameter “Country”:

    null meta [IsParameterQuery=true, List={"Germany", "Poland", "Belgium", "Hungary", "Italy"}, DefaultValue=..., Type="Text", IsParameterQueryRequired=false]

    and from there it was easy* to use

    List.Contains(Value.Metadata(Country)[List],SearchTerm)

    to get to the desired result.

    * as in “easy after reading this primer part”.

    Reply
  6. Ed Hoeffner

    Here’s a use for the meta “command”:

    “Opt 1” meta [IsParameterQuery=true, List={“Opt 1”, “Opt 2”, “Opt 3″}, DefaultValue=”Opt 1″, Type=”Text”, IsParameterQueryRequired=true]

    Not really sure how this works, but the format supplies a dropdown functionality. Personally, so far I only use it to switch in and out of test mode:

    false meta [IsParameterQuery=true, List={false, true}, DefaultValue=false, Type=”Logical”, IsParameterQueryRequired=true]

    It would be nice to really understand what’s going on here…

    Thanks for a very helpful blog!

    Reply
    1. Ben Gribaudo Post author

      From the mashup engine’s perspective, the above code samples simply merge metadata into the metadata of the specified value (e.g. “Opt 1”, false). These metadata fields have no special meaning to the mashup engine.

      Instead, the special behaviors you observe come from the fact that user interface components are programmed to display special things when those metadata items are set.

      Try this: In Query Edtior’s UI, create a parameter. Then, select the parameter in the Queries list and choose Advanced Editor. You’ll be able to see that the code the UI generated for the parameter is very similar to what you show above.

      You could play with the various options in the UI’s “create parameter” dialog then look at the code that’s generated to get a better feel for how various metadata fields affect/control how the UI behaves.

      Reply
      1. Ed Hoeffner

        Since the created record can take any fieldnames, these must be the special ones you’re referring to. Thanks!!

        Reply
  7. Filippo Bottega

    Hello and thank you for your useful post!
    my question is: is there a way to get the metadata (Description for example) of queries (members of section1) ? It seams that only standard functions could be checked for metadata.

    Reply
    1. Ben Gribaudo Post author

      If you’re referring to the description that Query Editor allows you to set by right-clicking on a query (section member) name and choosing Properties, then I don’t believe so. These descriptions are stored as literal attributes on section members in the section document. I’m not aware of a standard library function that allows them to be read out.

      Reply
  8. Rick

    Hey Ben. Great stuff.

    So one can look at metadata from a function name like this:
    Value.Metadata( Value.Type( [ColumnWithFunction ) )

    For example when using #shared to inspect data. What I haven’t been able to pinpoint, is how to return function syntax as a text value. I know it’s in there, since I can preview the [Function] value in the “Value” column.

    Do you know a way to return the syntax of a function as a text value?

    Reply
    1. Ben Gribaudo Post author

      By “syntax of a function,” do you mean the function’s signature? If so, I’m not aware of a single method that mimics what Query Editor’s UI renders to screen; however, you can put together that information using the three Type.Function* methods.

      Reply

Leave a Reply to Filippo Bottega Cancel reply

Your email address will not be published. Required fields are marked *