Shedding Light on the Mysterious Embedded.Value

“Accesses a value by name in an embedded mashup”—documentation’s terse description of this function raises almost as many questions as it answers. Let’s try to clear up at least some of the puzzle around this function. While we’re at it, we’ll uncover some surprises—did you know this function breaks an M language rule and that it exposes something never otherwise seen (as far as I’m aware) by end-user mashups?

Imagine: You are designing a way to share Power Query logic—in essence, creating a catalog of sorts storing common M code in a central location. Mashup tools (Microsoft Power BI, Microsoft Excel, etc.) will access this repository, pull down the mashup of interest and execute it locally. Your design could have client tools always downloading expressions using a live connection to the catalog, but this has downsides. What about when the client doesn’t have a network connection (e.g. when flying on an airplane)? What about when a shared expression’s definition changes before the local expression that consumes it is ready to be updated to work with the revision? Some means to locally cache downloaded definitions would solve both problems….

In times past, both Power BI and Excel supported a feature like this. Embedded content was an infrastructure piece used to support query sharing (possibly it was once used to support functionality, as well; I’m not aware of this happening, but can’t say for sure). While the idea of a shared query catalog has been discontinued, tooling still quietly supports embedded content—and this is a good thing, as it means that reports and spreadsheets that reference shared queries can still run using the cached copy embedded in the pbix or xlsx even though the central repository is gone.

Now that you have context, let’s look at how embedded content works. If you’d like to follow along, download a sample Power BI or Excel file and open it using Data Mashup Explorer.

File Format

In the data mashup binary stream, each embedded content item, or part, is contained under /Content in an Open Package Conventions (OPC) zip file with a GUID as its name. This GUID corresponds with the first argument passed to Embedded.Value. So, Embedded.Value("5ee9ae84-dacc-41f1-9b1b-9b877b864e77") accesses the embedded content contained in /Content/5ee9ae84-dacc-41f1-9b1b-9b877b864e77.

From what I’ve seen, these embedded OPC zip files use a format that is a variation of data mashup binary stream’s package parts. /Formulas/Section1.m (contains the Power Query section document defining the embedded mashups) and /Config/Package.xml (holds a few details related to the client) should be familiar from package parts, while /Config/Formulas.xml is unique to embedded items.

This Formulas.xml is what controls which query in the embedded item’s section document is exposed by Embedded.Value. The query marked Published="true" is evaluated and its value returned when Embedded.Value is invoked with the content item’s GUID. Only one query in each embedded item may be marked as published.

Example Formulas.xml:

<?xml version="1.0" encoding="utf-16"?>
<Formulas xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Formulas>
    <Formula SectionName="Section1" FormulaName="Helper" Published="false">
      <Resources />
      <Embeddings />
    </Formula>
    <Formula SectionName="Section1" FormulaName="ProduceOutput" Published="true">
      <Resources />
      <Embeddings />
    </Formula>
    <Formula SectionName="Section1" FormulaName="Message" Published="false">
      <Resources />
      <Embeddings />
    </Formula>
  </Formulas>
</Formulas>

Just as you might build a mashup that references other M queries, an embedded mashup can do the same. Any queries referenced by that item’s published query are also included in the embedded item’s section document. In that document, the published query needs to be shared; other queries, if present, need not be shared.

User Interface Behaviors

Query Editor’s GUI user interface gives no clues that embedded content is present, nor does it provide a way to view the embedded section document. The UI does, however, give special handling to embedded items in two areas:

Copy and Paste

In Query Editor, if you copy a query that references embedded content using Embedded.Value, behind the sense the referenced content is included in what’s copied. Pasting into another Query Editor quietly pastes the embedded item along with the query you copied. (Interestingly, if you paste into Power BI’s Query Editor, you may need to disable then reenable “Enable load” before closing and applying to keep Power BI from erroring.)

However, if instead you paste into another tool (say your favorite text editor), the embedded content is not included. Behind the scenes, when you copied the query, it was placed on the clipboard in several formats. When you pasted, your text editor most likely retrieved one of the common formats, which contained just the query, formatted as plain text. Query Editor, on the other hand, understands a special Power Query-specific format which uses a more complex structure to transmit the query along with the embedded content it uses. This format, identified in the clipboard’s format list as “Microsoft Mashup Format”, is defined in [MS-QDEIF]: Query Definition Interoperability Format. (Note: As of this writing, MS-QDEIF’s example showing embedded content is incorrect.)

Delete

In Query Editor, if you delete the last query that uses Embedded.Value to reference a given embedded content item, the content item is deleted. If, somehow or other, an embedded item manages to exist in the data mashup binary stream without a query referencing it, Query Editor will leave it quietly lying hidden in the file; subsequent Query Editor use won’t cause it to be cleaned up.

Power Query Behaviors

Embedded.Value’s Value

The most obvious behavior is Embedded.Value, which returns the value (table, record, string, function, etc.) produced by evaluating the embedded expression.

Sections

What may not be immediately obvious is that the presence of embedded content in the data mashup binary stream causes something to be exposed that, to my knowledge, is never otherwise exposed in an end-user Power Query environment: multiple sections.

The user’s mashups are always in Section1. This still holds true when embedded content is present. However, each embedded content item is exposed in its own section!

These sections are named in the format of __inlined__{GUID}.Section1. You can see them by evaluating #sections. A UI limitation may prevent their entire names from being displayed on screen; if this occurs, to view their full names, try Record.FieldNames(#sections).

An embedded item’s section contains all queries in the embedded item—both published and non-published, both shared and non-shared.

#sections[#"__inlined__5ee9ae84-dacc-41f1-9b1b-9b877b864e77.Section1"]

Section access expressions can be used to reference the expressions in this section. (Note: The embedded item’s section name needs to be given as a quoted identifier due to its syntax.)

// returns the value produced by evaluating Message: "Hello world!"
#sections[#"__inlined__5ee9ae84-dacc-41f1-9b1b-9b877b864e77.Section1"][Message]

// defines variable SomeFunction as a reference to Helper from the embedded item, then invokes this function
let
    SomeFunction = #sections[#"__inlined__5ee9ae84-dacc-41f1-9b1b-9b877b864e77.Section1"][Helper]
in
    SomeFunction(3)

This means you can even programmatically get an embedded function’s abstract syntax tree.

// returns the abstract syntax tree for the embedded function Helper
ItemExpression.From(#sections[#"__inlined__5ee9ae84-dacc-41f1-9b1b-9b877b864e77.Section1"][Helper])

Non-Existent Embedded Content References

When Embedded.Value is passed a single argument and that value does not correspond to the GUID of an embedded content item, the error that’s raised is interesting. Its message doesn’t say anything about the item not existing. Instead, it complains about the function being passed one argument when two were expected.

Embedded.Value("does not exist") // Expression.Error: 1 arguments were passed to a function which expects 2.

On the other hand, if the function is used with two arguments (more on this in a moment) and the first argument doesn’t reference the GUID of an embedded content item, no error is raised. Instead, the first argument’s value is returned.

Embedded.Value("does not exist", "some path") // returns "does not exist"

I’m not sure why these behaviors where chosen instead of simply raising an error saying that the specified embedded content item could not be found.

Rule-Breaking Argument Handling

Embedded.Value is defined as a two-argument function, with both arguments being required. According to the language specification, the mashup engine should raise an error when a function is invoked without all required parameters being provided. Somehow or another, Embedded.Value is an exception to this rule: the mashup engine allows it to be invoked with only one argument without complaint.

Mysteries

Two-Argument Version

Speaking of Embedded.Value‘s arguments, I don’t recall ever seeing an example of it being used with two arguments. I am curious about the second argument—what its purpose is/what it does.

If you have an example of Embedded.Value used with two arguments and don’t mind passing it on, I would be most interested in taking a look.

Formulas.xml’s <Embeddings> and <Resources>

These XML elements pique my curiosity. If you have an example that uses either or both of these and don’t mind passing it on, I’d be most interested to take a look.

Conclusion

Hopefully, Embedded.Value is now less mysterious. While it may be something you’ll never use in the future (unless Microsoft introduces another feature that relies on it), now at least you don’t need to wonder about what it is or the basics of how it works.