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.