今回は、開始時間・終了時間・担当者を可変で指定できる月間スケジュール表（記入用）を、Power QueryのM言語の技術をフル活用して作成します
4月1日であれば、13時から15時まで1時間刻みで行が自動で用意されます
担当者も可変にできます
一番のポイントは「開始」「終了」時間の判定列を、日付（1~31）と時刻（0：00～24：00）を網羅した表に作成することです
しかも判定用の列は2列用意します
判定列を作るためには、複数のクエリを用意し、クエリ内にキー列を作成しておくこともとても重要です
キー列：yyyy/MM/dd hh:mm
内容的に大きめの内容なので、今回はポイントの羅列で失礼させて頂きますので予めご了承ください
Power Queryでここまでできるのか？という点を感じて頂けたら幸いです
今回の内容の参考記事についても事前に紹介させて頂きます
見出しがセル結合により2行になってしまっている表をデータ活用１～上級編2回目～
【パワークエリ・チャレンジ】1列のデータから複数列のテーブルデータを作成する
目次
ポイント
各種データの用意
今回、想定する作業は以下のスケジュール表から、必要な時間だけ記載され、担当者も記載された完成スケジュール表を作成することです
下の画像が完成スケジュール表です
実際にPower Queryのクエリを作成する前に、３つの表（日付データ、時刻データ、担当者）を用意します
日付データと時刻データについては、あとで組み合わせて、次のようなクエリ（日付時刻クエリ）を作成します
こちらの結合済みの列は後でスケジュール表とマージして「開始時間」「終了時間」を紐づけるためのキー:yyyy/MM/dd hh:mmになります
日付データ
4月のスケジュールであれば、1日から30日までを用意します
*ホームタブの連続データの作成を活用するとデータの用意が早いです
時刻データ
どの月かは別にして0:00～24:00までを用意しておきます
こちらは固定になります
担当者
こちらも用意します
日付時刻クエリとスケジュール表クエリのマージ
前述のように日付と時刻を組み合わせて作成した日付時刻クエリはスケジュール表クエリとマージして、開始時刻と終了時刻を紐づけます
マージするためのキーはyyyy/MM/dd hh:mmになります
スケジュール表をマージするためにも、事前にスケジュール表はピボット解除を行っておきます
そして日付と時刻の列はマージして「yyyy/MM/dd hh:mm」のキー列を作成しておきます
フィル機能の活用
キー列をもとにして、２つのクエリをマージするとキー列に「開始時間」「終了時間」を紐づけることができます
但し、開始時間と終了時間を判定するには、判定列が1列だけでは不十分です
1列だけだと、終了⇒開始（上の画像だと16時以降）までの時間帯が判定できないからです
フィル機能を使って、下に埋めた列を作成します
2列あれば、残すべき行を特定できます
IF式
判定列を利用して、残すべき行を判定します
判定２が開始の場合には行を残します。判定１と２が終了の場合にも残します
＊１の場合は行を残す、０の場合は行を削除する
逆にそれ以外は行を残しません
担当者の紐づけ
担当者はカスタム列を作成してリストで紐づけを行います
＊担当者はクエリ名です
リストで紐づけを行い、展開した状態が下の画像の状態です
ピボット処理
担当者の列の内容は、下の画像のように見出しにします
この表の値欄は空欄になるので、ダミーの列を事前に追加しておくのも大きなポイントです
その後にピボット処理を行います
最後に
以上が今回のポイントなのですが、作成していくと、ところどころで並び順が変わってしまうと思います
その際には、インデックス列を追加して調整を図ったください
今回は長文に最後までお付き合い頂き誠にありがとうございました
参考までに今回のサンプルファイルを添付します
コメントを残す