Internals of Copy & Paste in Power Query Editor

[Nolock]

Today I will deep dive into an elementary feature called Clipboard. You use it probably hundred times a day using the keyboard shortcut Ctrl+C and Ctrl+V. But do you know what happens behind the curtains in the Power Query Editor when you copy a query?

What is a clipboard in the computer science terminology?

Let´s ask Wikipedia. It says: “The clipboard is a buffer that some operating systems provide for short-term storage and transfer within and between application programs. The clipboard is usually temporary and unnamed, and its contents reside in the computer's RAM. The clipboard is sometimes called the paste buffer.” (https://en.wikipedia.org/wiki/Clipboard_(computing))

How does it work internally?

You know that you can copy text, an image, data from a spreadsheet and paste it somewhere again and it works somehow magically. You can copy a spreadsheet and paste it into Notepad, but it does not work for an image. How is it possible?

When you insert data into clipboard, the source application stores this piece of data in multiple available formats. When you paste it, the destination application chooses the format it can process.

That is also the reason why you cannot paste an image into Notepad. There is no plain text in the clipboard as you can see on the following screenshot.

But if you copy a URL from Microsoft Word, there will be a plain text too, right next to the rich text.

In many Microsoft Office products, you can even choose what should happen with the pasted data.

Clipboard in the Power Query Editor

I have prepared 3 simple queries which are placed in groups. (By the way, the groups are not a part of the M language, but of the GUI.)

Let´s select all queries. Ctrl+A does not work but you can click on the top or the bottom query and use arrows together with Shift to select them all.

Copy these queries and paste them into Notepad.

Well, great. We have created a local copy of the M code of our queries. Every paragraph starts with a comment containing the name of the query. But we have lost the information which group the query belongs to. What is more important, we have also lost the information about the loading settings. Is the load of a query enabled or disabled? We do not know any more.

But wait a sec. If I select a query, copy and paste it in Power Query Editor, it creates a copy which preserves all settings.

And paste it.

Or another example. If I have 2 PBIX files opened and I copy some queries from one window into another, all settings are preserved. Even the groups! Well, how is it possible?

On the attached screenshot, we can see many formats. The first one is a plain text we have already seen in the previous chapter.

The more useful is the HTML format, which contains more details.

I have replaced the encoded characters like < to get the result XML file.

<?xml version="1.0" encoding="utf-8"?>
<Mashup xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns="http://schemas.microsoft.com/DataMashup">
    <Client>PBIDesktop</Client>
    <Version>2.79.5768.721</Version>
    <MinVersion>1.5.3296.0</MinVersion>
    <Culture>de-DE</Culture>
    <SafeCombine>true</SafeCombine>
    <Items>
        <QueryGroup Name="Group L1">
            <Description />
            <Items>
                <QueryGroup Name="Group L2">
                    <Description />
                    <Items>
                        <Query Name="Query (Group L2)">
                            <Description />
                            <Formula><![CDATA[let
    Source = 2,
    #"Converted to Table" = #table(1, {{Source}})
in
    #"Converted to Table"]]></Formula>
                            <LoadToReport>true</LoadToReport>
                            <IsParameterQuery>false</IsParameterQuery>
                            <IsDirectQuery>false</IsDirectQuery>
                        </Query>
                        <Query Name="Query (load disabled)">
                            <Description />
                            <Formula><![CDATA[let
    Source = 3,
    #"Converted to Table" = #table(1, {{Source}})
in
    #"Converted to Table"]]></Formula>
                            <LoadToReport>false</LoadToReport>
                            <IsParameterQuery>false</IsParameterQuery>
                            <IsDirectQuery xsi:nil="true" />
                        </Query>
                    </Items>
                </QueryGroup>
            </Items>
        </QueryGroup>
        <Query Name="Query (no group)">
            <Description />
            <Formula><![CDATA[let
    Source = 1,
    #"Converted to Table" = #table(1, {{Source}})
in
    #"Converted to Table"]]></Formula>
            <LoadToReport>true</LoadToReport>
            <IsParameterQuery>false</IsParameterQuery>
            <IsDirectQuery>false</IsDirectQuery>
        </Query>
        <Query Name="Query (load disabled) (2)">
            <Description />
            <Formula><![CDATA[let
    Source = 3,
    #"Converted to Table" = #table(1, {{Source}})
in
    #"Converted to Table"]]></Formula>
            <LoadToReport>false</LoadToReport>
            <IsParameterQuery>false</IsParameterQuery>
            <IsDirectQuery xsi:nil="true" />
        </Query>
    </Items>
</Mashup>

You see there are groups, their hierarchies, and all the settings of queries like LoadToReport, IsParameterQuery, or IsDirectQuery.

The same but in a more computer friendly format is in the last one called Microsoft Mashup Format.

If you do not have the tool InsideClipboard, you can also use Powershell. There is a command called Get-Clipboard which prints out the content of the clipboard in the available formats.

And there is more!

You can use such a boring feature like clipboard for faster debugging of your M code. Imagine you have a dataset with a lot of queries, you use references for ETL steps, and you want to extract only the queries which are predecessors of the loaded one.

How do you do it? Go to the query of your interest and copy it. When you paste the clipboard into an empty Power Query Editor, not only the selected query will be pasted, but all the predecessors, too. That is great, isn’t it?

You can use it for debugging a small part of a complex dataset. Or it is also useful when you want to see only dependencies of predecessor queries and not dependencies of all queries in your complex dataset.

Final words

I hope you have learned new ways how to use a clipboard in Power Query Editor. Do you know what happens under the hood when you click on Enter data in New Query in Power Query Editor? Then check out my other deep-dive blog post Power Query Enter Data Explained.

[Rudz]

This was very helpful. It's worth noting that the Microsoft Mashup Format is the one that Power BI and Excel use when you paste queries into a file. (You can test this by creating a .clp file from InsideClipboard, modifying it with a HEX Editor, reloading the .clp file in InsideClipboard, and then pasting into Power BI. I modified the CF_TEXT, HTML Format, and Microsoft Mashup Format and the MMF one is the one that was used.)

I was trying to copy ~200 queries from an export of a SSAS Tabular model into Power BI. You have to copy them in the MMF format for the paste to work. Turns out the easy way to do this is to create the queries in Excel with a VBA macro and then manually copy them over to Power BI. Thanks for this article or I would still be beating my head against a wall trying to get the HTML Format to work!

[Anonymous]

@Rudz 

I'm trying to set a code you can copy as a query in Power BI query editor with no success.

Would you have more details on how to fill a clipboard with relevant format? ideally programmatically?

Thanks and regards