Power Queryのクエリコピーの小ネタと何故かディープダイブ

Last updated at 2024-12-22Posted at 2024-12-17

Microsoft Power BI Advent Calendar 2024 の18日目の記事です。

小ネタ紹介記事で済ませようと思ったら、変な方向に突っ走ってしまった。

小ネタ1: Power Queryエディター間のコピペ

Power BI、データフロー、ExcelのPower Queryエディター間でクエリのコピペが相互に可能。

Power BIからExcelにコピペしてみる。逆方向やデータフローとのコピペも同様。結構便利。

複数のクエリでテストしたかったので、いつものようにヘルパークエリのやつを作成（後でやる実験のためにクエリ名を英語にしている）。

source クエリをコピー（右クリックから 複製 または Ctrl+C）して、ExcelのPower Queryエディターに貼り付けてみる。すると、関連するクエリも貼り付けられる。

今度は、ヘルパークエリを含むすべてのクエリをコピーして貼り付けてみる。期待通り、グループ構造も含めてコピーされる。

ただし、ヘルパークエリの便利機能である、「関数の作成…」は引き継がれないので注意。下図のとおり、「関数の作成…」がグレーアウトされておらず、カスタム関数を作成できるようになっている。

「関数の作成…」については以下の記事を参考に。

小ネタ2: Power Queryエディターからテキストエディターへのコピペ

実はVisual Studio Codeなどのテキストエディターに貼り付けることも可能。Visual Studio Codeを併用してコーディングする際は非常に便利。

ただし、 逆方向（テキストエディターからPower Queryエディター）は不可 。

先の一つ目のパターンで、テキストエディターにコピペすると次のようになる（ファイルパスは修正）。Power Queryエディターに貼り付けた時と同様、関連するクエリもコピペされている。

sourceクエリをコピペ（折りたたみ）

sourceクエリをコピペ

// source
let
    Source = Folder.Files("C:\Users\...\2412 Qiita Post - Copy Query in Power Query\source"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "transform file", each #"transform file"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "transform file"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "transform file", Table.ColumnNames(#"transform file"(#"sample file")))
in
    #"Expanded Table Column1"

// transform file
let
    ソース = (#"parameter 1" as binary) => let
    Source = Excel.Workbook(#"parameter 1", null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])
in
    #"Promoted Headers"
in
    ソース

// sample file
let
    Source = Folder.Files("C:\Users\...\2412 Qiita Post - Copy Query in Power Query\source"),
    Navigation1 = Source{0}[Content]
in
    Navigation1

同様に、クエリ全体をコピペすると次のようになる。必要なクエリはすべて含まれているものの、グループ構造がわからないのはなぜだろう？？

クエリ全体をコピペ（折りたたみ）

クエリ全体をコピペ

// sample file
let
    Source = Folder.Files("C:\Users\...\2412 Qiita Post - Copy Query in Power Query\source"),
    Navigation1 = Source{0}[Content]
in
    Navigation1

// parameter 1
#"sample file" meta [IsParameterQuery=true, BinaryIdentifier=#"sample file", Type="Binary", IsParameterQueryRequired=true]

// transform file
let
    ソース = (#"parameter 1" as binary) => let
    Source = Excel.Workbook(#"parameter 1", null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])
in
    #"Promoted Headers"
in
    ソース

// transform sample file
let
    Source = Excel.Workbook(#"parameter 1", null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])
in
    #"Promoted Headers"

// source
let
    Source = Folder.Files("C:\Users\...\2412 Qiita Post - Copy Query in Power Query\source"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "transform file", each #"transform file"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "transform file"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "transform file", Table.ColumnNames(#"transform file"(#"sample file")))
in
    #"Expanded Table Column1"

なんかいろいろと疑問が湧いてくる。

ディープダイブ

以下、公式情報による裏取りは一切していないので注意。ポエムと同じ。

着眼点

『スーパーくいしん坊』の主人公、香介くんの気持ちになってみた。

香介: 「出来らあっ！」
コック: 「いまなんていった？」
香介: 「クエリのコピーでディープダイブしてやるっていったんだよ!!」
コック: 「こりゃあおもしろい小僧だぜ
大勢のお客の前でケチをつけられたんだ
こりゃあどうしてもクエリのコピーでディープダイブしてもらおう」
香介: 「え!!　クエリのコピーでディープダイブを!?」

以下が気になったポイント。どうしても理由が知りたい。

Power Queryエディターどうしのコピペは双方向なのに、テキストエディターとは一方通行？
クエリのグループ化の情報がどうコピペされているか？
「関数の作成…」がなぜ途切れるのか？

準備

Windowsのクリップボードの仕組み

コピペの話なので、そもそもクリップボードがどう動いているのかを知るところから始めないと。

かなり古い記事ではあるが、以下から引用。

クリップボードの動作原理
クリップボードは、異なるプログラムからアクセス可能な一種の共有メモリとして実装されている。データを転送するアプリケーションは、できるだけ多くのアプリケーションがデータを取得できるように、さまざまな形式のデータをクリップボードに転送する。それを取り出す側のアプリケーションは、クリップボードに格納されたさまざまな形式の中から、情報損失が最も少ないものを取り出す。

Power Queryエディターでコピーして、テキストエディターに貼り付けたものを再度コピーしても、Power Queryエディターに貼り付けることはできなかった。

「え？同じものをコピーしているのに？？」と思ったが、上記の説明で納得。ペーストした結果が同じでも、Power Queryエディターでコピーした時と、テキストエディターでコピーした時で、クリップボードに格納されているデータが異なることがあるということ。

コピペが一方通行になったのは、その違いによるものではないかと。

違いを確認するためには、クリップボードの中身を知る必要がある。ということで次。

クリップブックの代替

先の記事で、クリップボードの中身をしるアプリとして、クリップブック（clipbrd.exe）が紹介されている。

しかし、手元のPCではクリップブックを使えなかったので（Windows 10以降は無理？）、フリーソフトのFree Clipboard Viewer 4.0を使用。

インストールするまでもないので、zip版で。
（だんだん沼にはまっていく…）

コピペが双方向だったり一方通行だったりする理由

さっそく、Free Clipboard Viewerを使って、クリップボードのデータを確認していこう。

Power Queryエディターでクエリをコピーしてみる

単純な例から調べたいので source file クエリをコピーしてみる。 プレビュー を選択している状態だと、テキストエディターに貼り付けたときと同じようなものが見える。

Microsoft Mashup Format というそれらしいやつを選択すると、XMLっぽいやつを確認できた！

XMLを整形してみると、

source 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.138.184.0</Version>
    <MinVersion>1.5.3296.0</MinVersion>
    <Culture>ja-JP</Culture>
    <SafeCombine>true</SafeCombine>
    <Items>
        <Query Name="sample file">
            <Formula>
                <![CDATA[
                    let
                        Source = Folder.Files("C:\Users\...\2412 Qiita Post - Copy Query in Power Query\source"),
                        Navigation1 = Source{0}[Content]
                    in
                        Navigation1
                ]]>
            </Formula>
            <LoadToReport>false</LoadToReport>
            <IsParameterQuery>false</IsParameterQuery>
            <IsDirectQuery xsi:nil="true" />
        </Query>
    </Items>
</Mashup>

なるほど、テキストエディターに貼り付けたときよりも多くの情報が含まれている。逆に、テキストエディターのをコピーしても、見えている情報しか含まれない（当たり前）。

つまり、Power Queryエディターでコピーした時と、テキストエディターでコピーした時で、クリップボードに格納されているデータが異なっていて、後者の場合はPower Queryエディターが必要とする情報が足りないため、コピペが一方通行となっているみたい。納得。

グループ構造がコピペされる理由

今度は、グループの構造がコピペで保存される理由を確認しよう。テキストエディターに貼り付けたときには、グループの情報はなくなっていた。それがクリップボードだとどうなっているかを見てみる。

全部は長すぎるので、個々のクエリの中身は省略。おお、確かにグループ構造がXMLで記述されている！

全体コピー（抜粋）


<?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.138.184.0</Version>
    <MinVersion>1.5.3296.0</MinVersion>
    <Culture>ja-JP</Culture>
    <SafeCombine>true</SafeCombine>
    <Items>
        <QueryGroup Name="Transform files from folder">
            <Description />
            <Items>
                <QueryGroup Name="Helper Query">
                    <Description />
                    <Items>
                        <Query Name="parameter 1">
                            ...(省略)...
                        </Query>
                        <Query Name="sample file">
                            ...(省略)...
                        </Query>
                        <Query Name="transform file">
                            ...(省略)...
                        </Query>
                    </Items>
                </QueryGroup>
                <Query Name="transform sample file">
                    ...(省略)...
                </Query>
            </Items>
        </QueryGroup>
        <Query Name="source">
            ...(省略)...
        </Query>
    </Items>
</Mashup>

全体コピー（省略なし）（折りたたみ）

全体コピー（省略なし）

<?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.138.184.0</Version>
    <MinVersion>1.5.3296.0</MinVersion>
    <Culture>ja-JP</Culture>
    <SafeCombine>true</SafeCombine>
    <Items>
        <QueryGroup Name="Transform files from folder">
            <Description />
            <Items>
                <QueryGroup Name="Helper Query">
                    <Description />
                    <Items>
                        <Query Name="parameter 1">
                            <Formula><![CDATA[
                                #"sample file" meta [IsParameterQuery=true, BinaryIdentifier=#"sample file", Type="Binary", IsParameterQueryRequired=true]
                            ]]></Formula>
                            <LoadToReport>false</LoadToReport>
                            <IsParameterQuery>true</IsParameterQuery>
                            <IsDirectQuery xsi:nil="true" />
                        </Query>
                        <Query Name="sample file">
                            <Formula><![CDATA[
                                let
                                    Source = Folder.Files("C:\Users\...\2412 Qiita Post - Copy Query in Power Query\source"),
                                    Navigation1 = Source{0}[Content]
                                in
                                    Navigation1
                            ]]></Formula>
                            <LoadToReport>false</LoadToReport>
                            <IsParameterQuery>false</IsParameterQuery>
                            <IsDirectQuery xsi:nil="true" />
                        </Query>
                        <Query Name="transform file">
                            <Formula><![CDATA[
                                let
                                    ソース = (#"parameter 1" as binary) => let
                                    Source = Excel.Workbook(#"parameter 1", null, true),
                                    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
                                    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])
                                in
                                    #"Promoted Headers"
                                in
                                    ソース
                            ]]></Formula>
                            <LoadToReport>true</LoadToReport>
                            <IsParameterQuery>false</IsParameterQuery>
                            <IsDirectQuery xsi:nil="true" />
                        </Query>
                    </Items>
                </QueryGroup>
                <Query Name="transform sample file">
                    <Formula><![CDATA[
                        let
                            Source = Excel.Workbook(#"parameter 1", null, true),
                            Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
                            #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])
                        in
                            #"Promoted Headers"
                    ]]></Formula>
                    <LoadToReport>false</LoadToReport>
                    <IsParameterQuery>false</IsParameterQuery>
                    <IsDirectQuery xsi:nil="true" />
                </Query>
            </Items>
        </QueryGroup>
        <Query Name="source">
            <Formula><![CDATA[
                let
                    Source = Folder.Files("C:\Users\...\2412 Qiita Post - Copy Query in Power Query\source"),
                    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
                    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "transform file", each #"transform file"([Content])),
                    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
                    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "transform file"}),
                    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "transform file", Table.ColumnNames(#"transform file"(#"sample file")))
                in
                    #"Expanded Table Column1"
            ]]></Formula>
            <LoadToReport>true</LoadToReport>
            <IsParameterQuery>false</IsParameterQuery>
            <IsDirectQuery>false</IsDirectQuery>
        </Query>
    </Items>
</Mashup>

コピペで「関数の作成…」が途切れる理由

Power BIのpbixファイルをpbip形式で保存し、TMDLで確認する。「関数の作成...」が有効かどうかで差分が生じるファイルを探す。

結果、 filename.SemanticMocel/definition/expressions.tmdl というファイルで差分が生じていた。

expressions.tmdl（変更箇所を抜粋）

...（省略）...

expression 'transform file' =
		let
			ソース = (#"parameter 1" as binary) => let
				Source = Excel.Workbook(#"parameter 1", null, true),
				Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
				#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])
			in
				#"Promoted Headers"
		in
			ソース
+	mAttributes: [ FunctionQueryBinding = "{""exemplarFormulaName"":""transform sample file""}" ]
	lineageTag: 2edfa38f-32d4-436b-8cec-05166b5ece78
	queryGroup: 'Transform files from folder\Helper Query'

	annotation PBI_ResultType = Function

...（省略）...

mAttributes: [ FunctionQueryBinding = "{""exemplarFormulaName"":""transform sample file""}" ] の意味は、関数クエリ transform file は、クエリ transform sample file に紐づけられている（バインドされている）ということかと。

で、この情報はクエリをコピーしたときに、クリップボードのMashup Formatには含まれていなかった。だから、関数の紐づけはコピペで切れてしまっていた。

感想

息切れ気味だったので、小ネタに走るつもりが、なんかやたらと細かいことを調べてしまった。。そして大して役に立つ情報ではないという。。

ま、いっか＼(^o^)／

Microsoft Power BIAdvent Calendar 2024

Day 18

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

Power Queryのクエリ コピーの小ネタと何故かディープ ダイブ