タグ別アーカイブ: データ追加

M言語に慣れる_9回目~複数シートをコード1行で結合~

【M言語は難しくない】今回は、空のクエリM関数を1つ入力するだけで複数シートを一気に結合できるようにします!今回紹介するM関数を使いこなせば、面倒なシート間のデータのバケツリレーから抜け出すことができます

 Power Queryで複数シートを結合する方法として一般的に紹介されている方法では、次の画像の処理「クエリの追加」が必ず入ると思います

今回は、「クエリの追加」は使わず空クエリの数式バーに「=Excel.CurrentWorkbook()」を入力します

1つM関数を入力するだけで、ファイルに含まれるシート内のテーブルが全てエディタ内に反映されます

これで、シート間のバケツリレーの作業からは抜け出すことができます!

但し注意点もありますので、そちらもあわせてM関数/Excel.CurrenWorkBookの使用方法を解説します

目次

今回のポイント

今回使用するデータ

準備作業/空のクエリを作成

M関数の入力/Excel.CurrentWorkbook

読み込み処理

データの循環対策

シート追加テスト

<まとめ>

今回のポイント

今回はM関数を入力した後に、1点だけ注意点があります

それは、循環問題です

複数シートを1つにまとめた内容/クエリが、M関数に反映されてしまいます

ですので、クエリを更新すると読み込み行数が倍になります

対策として、適用したステップに1つのステップを追加します

今回使用するデータ

今回使用するデータには、シートが3つ含まれています

1シートが1か月分の出荷データになっています

各シートの内容は、事前に「シート名/Data_y年m月」をテーブル名にしてテーブル化してあります

次のファイルが今回、実際に使用するサンプルデータです

準備作業/空のクエリを作成

まずは空のクエリから作成します

「データタブ」➡「データの取得」の順でクリックします

その後、下の画像にて黄色に印をつけた箇所を上から順にクリックします

そして、下の画像にある「空のクエリ」をクリックします

するとエディタが開き、空のクエリが立ち上がります

M関数の入力/Excel.CurrentWorkbook

記事の冒頭でも解説しように、 「=Excel.CurrentWorkbook()」を入力します

上の画像のように3つテーブルが表示されたら、2つ処理をします

まず、上の画像の「Name」の列を削除します

次に下の画像の黄色に印をつけた箇所から、各テーブルの内容を展開します

展開する際には、次に開いた画面にて、「元の列名をプレフィックスとして使用します」のチェックを外しておきます

上の画像のOKボタンをクリックすると次の画像のように、各テーブルが展開します

出荷日については、データ形式を日時型式ではなく、日付形式にしておきます

読み込み処理

読み込み処理は、新規のテーブルに行います

上の画像の「閉じて次に読み込む」をクリックし、次に開いた「データのインポート」画面にて、新規のシートを指定します

新しく追加されたシートは、シート名を「小計」に変更しておきましょう!

データの循環対策

まず、新しく作成したクエリをエディタで開き直します

エディタを開いたら、適用してステップの「ソース」を選択します

すると、下の画像のように4つのテーブルが表示されています

循環対策として、テーブル名に「Data」を含むものだけが展開されるようにフィルターをかけるステップを追加します

フィルターをかける際には、下の画像のように「指定の値で始まる」を指定し、「指定の値」として「Data」を指定します

すると、次の画像のように「Data」から始まるテーブル名のテーブルだけが表示されます

ここまで、行ったらエディタを閉じて読み込みます

シート追加テスト

適切に循環対策が機能しているかどうかを調べるため、「Data_2020年4月」のテーブル、及びシートを追加します

追加するシートには1行だけデータがありますので、循環対策が適切であれば、更新処理時に1行だけが増えるはずです

では、更新を行ってみます

上のGIF画像のように、適切に1行増えています

<まとめ>

今回はM関数「=Excel.CurrentWorkbook()」を 空のクエリを入力して、各シートのテーブルを一括でまとめました

 後からテーブル/シートを追加したとしても「データの結合」処理を行わずに済むので大変楽な方法かと思います

但し、1点だけ注意点があり、それが循環対策です

 複数のテーブルをまとめた内容自体が、「まとめ」の内容に含まれないようにフィルター処理を追加しました

Power Queryにおけるエラー対策として、フィルター処理はとても有効です

この点は今回を機に、しっかりおさえておきましょう!

長文に最後までお付き合い頂き誠にありがとうございました

今回使用したファイルの完成版を添付しておきます

次回はグループ毎に連番を付与する方法を解説します

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

【豆知識】ピポット解除時の空欄の扱い

 パワークエリのピボット解除は便利ですよね。一括で縦横並びを縦縦並びに変えられます。ところが、元データに空欄が混じると2つ問題が出てきます。1つ目は空欄を含むデータが非表示になることです。

 こちらは「値置換」で対応できます。ところが元データに列を追加した時、追加した「値置換」が追加列に対して行われなくなります。これが2つ目の問題です。

 原因は値置換を行う時に、対象列名が記録されてしまっていて、追加列に対しては「値置換」が行われないのです。こちらはコードを直接置き換えることで対応できます。

今回の課題は、実際の業務ではあまり遭遇する機会が少ないかもしれませんが、Power Queryの「ハードコード」という特性を掴むにはとてもいい課題です

ぜひ、手を動かしてPower Queryの特性を掴んでみてください!

では、上記の①/1つ目の問題から②/2つ目の問題の順番で解説を行って行きます!

①空欄の置換

下の図は、上の表がピボット解除前の元データです

下の表は、元のデータをピボット解除したデータです

ピボット解除時は元データに空欄が含まれていると、空欄のデータは出力されません

例えば、図の下の表では「かつ丼の2020/01/02」の空欄データは抜けてしまっています

空欄データを「0」で表示したい時には、値置換で対応します

ピボット解除を行うクエリをPower Queryエディタ(以降、エディタ)で開くと、上の画像の図のように空欄は「null」で表示されます

こちらの「null」を「0」で置き換えます

置換処理を行う前は、Ctrl+Aで全データを選択しておいてください

値置換を行えば、下の画像のように空欄が0で表示されます

②コード変更

①で置換を行いましたが、次の画像のように元データに列を追加すると置換に関する問題が発生します

下の画像のように、追加された2020/01/08のかつ丼の空欄が0で反映されません

この理由は、Power Queryの特性であるハードコードにあります

ハードコードとは、エディタ内のステップに処理の内容がとともに記録されることです

では、0への置換を行ったクエリの中味をエディタで見てみます

上の画像は、空欄から0への置換を行ったコードです

このコードを見ると、0への置換処理は「2020/01/07」までの列に対して行うことが記録されています

 ですので、ハードコードされていない列、上記のコードに記載されていない列が追加されると「置換」は行われません

この状況をどのように改善するかというと、コード自体の置換を行います

まずは、列名が記載されたコードは削除します

その後削除したコードを、下の図のようにテーブル内の全ての「列名」を表示するTable.ColumnNamesで置き換えます

そうすれば、いくら列を追加しても置換が行われるようになります

今回の解説は以上になります

②のコード変更については、概要だけを解説しました

Power Queryの言語である「M言語」については、別途、シリーズで記事を書きますので、そちらでは詳細な解説を行いたいと思います

記事を最後まで読んで下さり、誠にありがとうございました

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村