永久休暇カレンダー – POTW #12 (Power Query ソリューション)

このブログ投稿では、に対する私の解決策を検討します。このチャレンジでは、LuckyTemplates の初の「今週の問題」チャレンジを再検討し、特定の日付と相対的な日付を組み合わせて永久休日カレンダーを作成しました。ただし、今回はPower Queryのみを使用します。このチュートリアルの完全なビデオは、このブログの下部でご覧いただけます。

問題の内訳そのものに行き詰まった場合は、再検討して、いずれかのアプローチにこの課題の解決に役立つ要素があるかどうかを確認してください。

投稿を読んでいると、さまざまな人々がユーザー インターフェイスを活用し、カスタム関数を作成して永久カレンダーを作成しているのを見てきました。各エントリーを分析するのは楽しいので、インスピレーションを得たい場合は、同じように分析することをお勧めします。

私自身このシナリオに取り組んでいたとき、最終的に複数のソリューションを作成することになりました。私が使用したものは、問題をより小さな部分に分割するだけでなく、それを可視化する方法を最もよく示しています。これは、エラーや予期しない結果が発生した場合に非常に役立ちます。

目次

• 永久カレンダー作成の出発点
• 日付テーブルから年を取得する
• 曜日列から数字を取得する
• 永久休日カレンダーのカスタム関数の呼び出し
• 日付のリストの作成
• 条件付きロジックの適用
• 永久休日カレンダーの M コードの確認
• 結論

永久カレンダー作成の出発点

この生の休日テーブルが私たちの出発点です。日付をまとめるには、特定の日付の年、月、日が必要です。足りないのは年だけです。

ただし、相対日付の場合は、もう少し作業が必要です。まず、その日の列を数字と曜日の両方に分割する必要があります。これらの手順を確認してみましょう。

日付テーブルから年を取得する

このソリューションを動的にするために、日付テーブルから年を取得します。数式バーでは、射影を使用して日付テーブルから単一列テーブルを取得したことがわかります。もちろん、これには重複が含まれます。これらを削除するために、 Table.Distinctを追加しました。

これらのテーブルのいずれかの横にある空白部分をクリックすると、日付テーブル クエリに存在する個別の年のリストも表示されます。

月の名前が与えられましたが、実際には月番号が必要になります。ここで、年を月の名前に連結するときに、Date.From関数を使用して、その月の最初の日を日付値として返すことができます。

Date.Monthを追加すると、その特定の日付の月番号だけが残ります。1 月の場合は 1 を返し、2 月の場合は 2 を返します。

曜日列から数字を取得する

日列から数値を取得するために、列を分割せずに値を抽出することにしました。数式バーでわかるように、Text.BeforeDelimiterを使用し、そのスペースを使用してテキスト文字列の右側と左側の部分を識別しました。

スペースがない場合は、日列に残っているものを返します。したがって、最初のレコードの場合は 1 が返されます。そのスペースを含む 2 番目のレコードの場合は、テキスト文字列の最初の部分であるThirdという単語が返されます。

同様のアプローチを使用して日付名を取得しますが、関数List.PositionOfを使用します。これは、最初の引数として渡されたリスト内の値の 0 から始まるオフセットを返し、値がそのリストにない場合は -1 を返します。

最初のレコードでは、リストに存在しない値 1 が渡されます。これが、値 -1 を返す理由です。2 番目のレコードの場合、値Mondayが返されます。

それを関数に渡し、リストの 2 番目の項目であるため 1 を返します。次に、すべての -1 値をnullに置き換えます。

この時点で、カスタム関数を呼び出すために必要なすべての構成要素が作成されました。

永久休日カレンダーのカスタム関数の呼び出し

数式バーでは、関数がyear、month、numeral、weekdayの 4 つの引数を取っていることがわかります。

しかし、その日付値はどのように決定されるのでしょうか? List.PositionOf を再度使用して、数値の数値を取得します。1 番目、2 番目、3 番目、4 番目を含むリストがあります。数値をList.PositionOfに渡すと、ゼロから始まるオフセットが返されます。

ここでも、値が存在しない場合は -1 を返します。したがって、最初のレコードには値 1 を渡します。このリストには 1 が存在しないため、値 -1 が返されます。2 番目のレコードには、リストの 3 番目の項目である値 Third を渡します。これにより、値 2 が返されます。

日付のリストの作成

それでは、日付のリストを作成してみましょう。その月の開始日を決定し、その月の日数を決定し、List.Dates を使用してその月のすべての日を含むリストを作成します。

次に、 List.Selectを使用して、特定の平日の日付のみを保持します。

これらのリストのいずれかの横をクリックすると、そのリストにはこれら 4 つの項目のみが含まれていることがわかります。

条件付きロジックの適用

最後に、条件付きロジックを適用して日付を構築または取得します。曜日がnullの場合は、特定の日付があることを示し、組み込み#date を使用して日付値を構築できます。

数値が最後の値と等しい場合は、List.Reverse関数を使用してリスト内の順序を逆にし、最初の値を抽出できます。

この昇順を降順に変更したい場合は、いつでもその日付範囲から最後の日付を抽出できます。リストに要素が 4 つか 5 つあるかどうかは関係ありません。順序を逆にして最初の項目を抽出すると、常に最後の日付が得られます。

他のすべての場合には、List.Skipを使用します。

リスト上のこの位置に基づいて値を抽出できます。感謝祭の場合、数字は 4 番目で、4 番目は 3 を返します。これにより、リスト内の最初の 3 つの値がスキップされ、常に 4 番目の項目が返されます。

すべてをまとめて、解決策のクエリに進みましょう。ソース ステップでは、RAW 休日日付テーブルを呼び出します。次に、日付のリストを返す列を追加します。

永久休日カレンダーの M コードの確認

列のMコードを確認してみましょう。テーブルの行ごとに、日付テーブルからの個別の年を含むテーブルをネストしました。次に、カスタム関数を呼び出して、その年の列の値を変換しました。

もちろん、カスタム関数を呼び出せるようにするには、最初にいくつかのセットアップを行う必要があります。月番号、数値、そしてもちろん曜日を取得する必要があります。次に、そのネストされたテーブルから、日付のリストを保持しました。

空白部分の横をクリックすると、そのリストのプレビューが下に表示されます。

次のステップでは、そのリストを展開し、CelebratedOnDateを追加します。

これがこの計算で使用したロジックです。日付が土曜日の場合は、金曜日になるように 1 日減算します。日曜日の場合は、月曜日に着陸するように 1 日追加します。土曜日または日曜日ではない場合は、null が必要です。

最後に、最後のステップはタイプを変更することです。

結論

LuckyTemplates フォーラムで毎月第 1 水曜日と第 3 水曜日に開催される継続的なシリーズであることを簡単に思い出してください。第 1 週目に投稿された問題では DAX ベースのソリューションが必要ですが、第 3 週目の問題では Power Query ベースのソリューションが必要です。

この POTW で取り上げたコンテンツを気に入っていただけた場合は、LuckyTemplates TV チャンネルに登録してさらにご覧ください。私自身やさまざまなコンテンツ作成者からの膨大な量のコンテンツが常に公開されており、すべて LuckyTemplates と Power Platform の使用方法を改善することに専念しています。

メリッサ