【M言語は難しくない！】今回はカスタム列の隠れた裏技を使用して、特殊なテンプレートを作成します。今回は次の画像のように、「1対多」の関係の結合を「コードを1つ追加する」だけでサクッと行います
この処理は、従来であればVBAなどを覚えないとできない処理でした
M言語の仕組みを使えば、カスタム列・作成画面にて本当にサクッと作成できます
M言語の魅力のうちの一つといってもいい処理かもしれませんね！
今回作成するテンプレート
次の画像のように「担当者名のリスト」と「勤務日のリスト」から、各担当者別の勤務表を作成します
上の画像の右側/E列とF列の表のように、担当者1人に対して、複数の勤務日を結合します
勤務表は、最終的には勤務日の右に「勤務開始時刻」や「勤務終了時刻」などを追加して使用するイメージです
解説は、シート上の「担当者名のリスト/列」と「勤務日のリスト/列」から次の２つのクエリを作成した上で、Power Queryエディタ（以降エディタ）で操作を開始するところからはじめます
・担当者名のリスト ➡ Namesクエリ
・勤務日のリスト ➡ TimeTable
次のファイルに含まれる演習用の元データは、クエリを作成する前の状態になっています
目次
今回のポイント
今回の「カスタム列」では、前述の通り「1対多」の関係で結合を行います
次の画像のように「列」ではなく「テーブル」を結合します
この「テーブル」を結合するの点が、今回の解説の最大のポイントになります
今回作成する内容とポイントを確認したところで、本格的な解説をはじめます
データ形式の事前調整
前述の通り、解説はエディタ内に２つのクエリがある状態から開始します
・担当者名/列のリスト ➡ Namesクエリ、・勤務日/列のリスト ➡ TimeTable
過去の記事で解説した通り、「データの構造化」が行われた結果、データ形式の変更が必要な箇所が1点でてきますので、まずはそちらを変更します
データ形式の変更は、上の画像の左上の黄色の印から行えます
上のGIF画像のように、日付を指定した後は次の画面が開きます
「現在のものを変換」をクリックすれば、下のGIF画像のように型式変更が行われます
各行に日付テーブル作成
では、「Names」クエリをエディタ画面の左から開いてください
次に「列の追加タブ」から「カスタム列」をクリックしてください
カスタム列の作成画面が下の画像のように開いたら、「新しい列名」を「勤務日」と指定してください
ところで、上の画像の右に「使用できる列」の欄があります
こちらには「担当者名」しか使用できる列がありません
挿入したいのは、勤務日であり、しかも担当者1人につき「1対多」の関係で結合する必要があります
ここからはM言語の出番です
M言語を書くといっても、実は、下の画像のようにクエリ名を指定するだけです
しかも、クエリ名を途中まで入力したところで、「入力補助」機能が働きます
クエリ名を指定したら、カスタム列作成画面の右下にあるOKボタンをクリックします
すると、記事の冒頭の「今回のポイント」で紹介した、テーブル結合が行われた画面になります
作成テーブルの展開
ここからは、中級編で解説したテーブル結合時の「展開処理」と一緒です
勤務日の右上のマークをクリックすれば、TimeTableクエリの内容が展開します
実際に上の画像のマークをクリックすると、実際には、下のGIF画像のように展開内容を指定する画面が開きます
こちらの画面では、展開項目として「勤務日」を指定するのはもちろんのこと、「元の列名をプレフィックスとして使用します」のチェックは外しておきます
こちらのチェックを外しておかないと「元のクエリの列名」も入ってしまいます
「元の列名をプレフィックスとして使用します」 のチェックを外したところで、画面下のOKボタンを押します
条件式の追加による調整
前述のテーブル展開を行った状態だと、下の画像のように、厳密には担当者名と勤務日が「1対多」の関係になっていません
上の画像の黄色に印をつけた箇所が、空欄になっていないといけません
こちらについては、黄色の箇所が空欄になる条件列を挿入します
勤務日は「2021/03/01」～「2021/03/03」の並びを繰り返しています
ですので、条件列は次の２つの内容になります
・勤務日が「2021/03/01」 ➡ 担当者名
・勤務日が「2021/03/01」以外 ➡ null（空欄）
では、上の２つの内容を、実際の条件列・作成画面に反映させます
条件列は、「列の追加」タブから「条件列」をクリックして作成画面を開きます
開いた作成画面に、前述２つの内容を設定していきます
まずは勤務日が「2021/03/01」の時の内容です
条件部分の「値」は「2021/03/01」を手入力します
あるいは、カレンダーから日付を選択することもできます
上の画像右の出力の箇所は、「列名」を選択できように、下の画像の「列の選択」を指定するのを忘れないようにしてください
次は 勤務日が「2021/03/01」以外の時の内容です
2点の内容を設定してOKボタンを押すと、エディタ内に新たな列が追加されています
シートへの読み込み処理
エクセルシートに読込む際には元の担当者名の列は削除し、追加した条件列を一番左に移動しておきましょう！
また、上の画像の「勤務日.1」の列は列名を「勤務日」にし、データ形式も「任意」/下画面左から「日付形式」/下画面右に変更しておきましょう！
では、実際にエクセルシートに読み込み処理を行います
＜まとめ＞
今回は、M言語を１つ入力するだけで特殊なテンプレートを作成する方法について解説を行いました
カスタム列の作成画面にて、クエリ名/TimeTableを指定するだけで「1対多」の関係によるデータ結合が行えました
その後は、通常のデータ結合時と同じように結合内容を展開しました
今回は、上記の仕組みを使用して「担当者名」と「勤務日」のリストから勤務表を作成しましたが、他にも活用できる場面があるはずです
何より、M言語の威力を肌で感じて頂けたら幸いです
今回の解説は以上です！！
長文に最後までお付き合い頂き、誠にありがとうございました
参考までに今回使用したファイルを添付します
次回は、M関数の参照機能について解説します
コメントを残す