Power Query の数式を使用して各日付の値を作成する

このチュートリアルでは、LuckyTemplates でPower Query の数式を使用して毎日の値を作成する方法を学習します。

日付の値を作成することは、LuckyTemplates で学ぶべき重要なテクニックです。これにより、モデル内で 1 日を通して確認したいデータが得られます。この技術は柱間の隙間もカバーします。

これは、日付テーブルに情報が不足している場合に使用する優れたツールです。

その方法についてのステップバイステップのガイドは次のとおりです。このチュートリアルの完全なビデオは、このブログの下部でご覧いただけます。

目次

• 追加の値を設定する
• ID のフィルター条件を作成する
• 日付のフィルター条件を追加する
• 日付列の確認と並べ替え
• Table.FirstN 関数の使用
• 2 つの日付値の間の日の差を取得する
• 体重ステップを決定する
• ギャップを埋める
• 日付の体重予測を取得する
• 結論

追加の値を設定する

ID、日付、および重みを含むデータ セットの場合、それらの測定値の間のギャップを埋める必要があります。これを行うには、日付範囲を作成して、日数にわたる差異を分散します。

これを行うには、Power Query に移動してテーブルを確認します。

データのコピーをメモリにロードする必要があります。そのためには、Table.Bufferメジャーを使用する必要があります。

「TableBuffer」をクリックすると、メジャーの内容が表示されます。

次に、データ セットに戻り、数式バーの[ fx]をクリックします。メジャーに Table.Buffer を追加する必要があります。

次に、このステップの名前をBufferedTableに変更します。

次に、カスタム列を追加します。

ID のフィルター条件を作成する

この Power Query の数式では、 BufferedTable を最初にチェックする必要があるため、参照されるステップをBufferedTableからRenameColumnsに変更します。

BufferedTable を確認するには、選択した条件に一致するテーブルを返すTable.SelectRowsを使用します。

したがって、数式内の式ごとに、Table.SelectRows を使用してから、BufferedTable を使用します。

フィルター条件としてカスタム終了関数を使用し、Buffered Table の BTを入力します。ID 列が現在のレコードの ID と等しいかどうかを確認します。次に、アクセス演算子としてアンダースコアを使用して現在のレコードにアクセスし、同じ ID 列を参照します。

これにより、現在のレコードに一致する ID のみを含むフィルター処理されたテーブルが得られます。 

日付のフィルター条件を追加する

現在のレコードと一致する日付のみが必要なため、日付列をフィルターする必要があります。

次の関数を使用して、BT 日付列を確認します。

この列は、現在のレコードの日付以降である必要があります。

これにより、テーブルには 3 行ではなく 2 行が含まれるようになります。

日付列の確認と並べ替え

Table.Sort を使用して、日付列の並べ替えが正しいかどうかを確認する必要があります。Table.Sort 関数は、必要な基準に応じて 1 つ以上の列でテーブルを並べ替えます。

この例の構文は非常に複雑なので、テーブルの件名をダウンドリルする必要があります。

テーブルを右クリックして、新しいクエリとして追加する必要があります。

これをクリックすると、そのテーブルを含むカスタム クエリが表示されます。

次に、日付列をクリックし、[昇順で並べ替え] を選択します。

式全体をコピーします。

メインクエリに戻り、それを数式バーに貼り付けます。

パラメーターを渡す必要があるテーブルは Table.SelectRows からの結果である必要があるため、Table.Sort の参照を削除する必要があります。

完了したら、並べ替え条件を再度追加します。変更を適用すると、テーブルが日付順に正しく並べ替えられるようになります。

Table.FirstN 関数の使用

このステップでは、3 つのレコードではなく 2 つのレコードだけが必要です。

これを修正するには、カスタム クエリに戻ります。「行を保持」をクリックし、次に「上位行を保持」をクリックします。

行数の欄には「2」を入力します。

Table.FirstN 関数が数式バーに表示されていることがわかります。Table.FirstN は、 countOrCondition の値に応じてテーブルの最初の行を返します。

その関数と必要な行数をメインクエリに挿入します。最後に関数タイプの表を含めます。

セット内のすべてのテーブル オブジェクトには 1 行または 2 行のいずれかが含まれます。

2 つの日付値の間の日の差を取得する

次の手順では、日付値の間の日数を取得し、2 番目のレコードにある日付を抽出します。

まずカスタム列を追加します。「Days」という名前を付けます。次に、次の Power Query の式に従います。

この構文では、テーブル内に 2 つのレコードが存在することが確認されます。1 だけの場合は、次のレコードがないため、日差は 0 になります。2 の場合は、日付の値から差分を取得できます。

次に、このように式に型番号を追加します。

ご覧のとおり、2017 年 9 月 14 日から 2018 年 10 月 4 日までには 385 日の差があります。

体重ステップを決定する

次に、毎日の体重を計算する必要があります。

カスタム列を再度追加し、「WeightStep」という名前を付けて、次の Power Query 式をコピーする必要があります。

構文は、テーブルに複数の行があるかどうかを確認し、現在のレコードから重みを減算し、その差を日数で割ります。

繰り返しになりますが、数式にタイプ番号を追加して、毎日の体重を確認できるようにします。

ギャップを埋める

日数がわかったので、データ セット内のギャップを埋める日付のリストを作成できます。

まず、別のカスタム列を追加し、DateKeyという名前を付けます。次に、次の Power Query の式に従います。

この数式は日付の違いがあるかどうかをチェックします。存在する場合は、現在のレコードからの開始日の間の日付のリストを作成し、それを日数分拡張します。ただし、違いがない場合は日付を返します。

結果は次のとおりです。

相違点のないテーブル サブジェクトには日付が返されました。実行されたテーブルは日付のリストを取得します。

DateKey を新しい行に展開すると、すべての日のレコードを表示できます。

日付の体重予測を取得する

日付リストを作成した後、日付の重み予測を取得する必要があります。

まず、カスタム列を追加し、WeightProjectionという名前を付けます。次に、次の Power Query 式を入力します。

構文は重み値を取得し、それを DateKey 値に加算し、日付値から減算して、WeightStep を乗算します。

もう一度、式の最後にタイプ番号を追加します。

これで、各日付の体重予測が得られました。

以下の式を使用してヘルパー列 Custom、Days、および WeightStep を削除し、DateKey 列の形式を Date に変更します。

結論

このチュートリアルでは、各日付リストに値を作成してデータ セット内のギャップを埋める方法を学習しました。

これらの日付値は、モデルから貴重な洞察を得るのに役立ちます。テーブルに情報が不足している場合は、この手法を使用してテーブルをより包括的にすることができます。

テーブル内に省略している重要なデータがある可能性があるため、常にすべての行と列を最大化してください。

ではごきげんよう、

メリッサ