LuckyTemplates で永続的に更新される実践データセットを作成する

更新するたびに練習データセットを更新し続ける方法を説明します。このチュートリアルの完全なビデオは、このブログの下部でご覧いただけます。

本当に DAX スキルを向上させたい場合は、DAX を使用して実践するのが最善の方法です。そのためには、起こり得る現実のシナリオを視覚化し、それに取り組むのに役立つ練習用データセットが必要です。

練習用データセットの問題は、データが通常静的であることです。効率的に練習するには、データが生きているという印象を与える必要があります。

目次

• 練習用データセットのファイル パスのセットアップ
• データ準備フォルダーの作成
• 日のオフセットを取得する
• 動的な日付範囲の設定
• 単一の売上テーブルの作成
• 注文日列の変換
• M コードを使用したタイム インテリジェンスの適用
• 結論

練習用データセットのファイル パスのセットアップ

サンプル データとして、2014、2015、2016 年の売上データを含む Excel ファイルがあります。

これは更新する必要があるデータです。

ドキュメントの場所は、クイック アクセス ツールバーにあります。

ファイルパスのパラメータを設定するにはこれが必要です。そこで、そのドキュメントの場所をコピーします。

その右側にクイック アクセス ツールバーへのアクセス ポイントがあります。

それを押すと、この画面が表示されます。

そこで、 [すべてのコマンド]を選択し、[ドキュメントの場所]を選択します。それをクイック アクセス ツールバーに追加し、[OK] を押します。

次に、 Power Queryに移動して、ファイル パスを保持するパラメーターを作成します。この手順は、別のユーザーがファイルの場所を変更した場合にモデル内のすべてのクエリを復元できるため、重要です。

そこで、新しいパラメータを作成します。

これをFileLocationと呼ぶことにします。

[タイプ] で[Text]を選択します。[現在の値]には、前にコピーしたドキュメントの場所を貼り付けます。

新しいクエリを作成してみます。

次に、練習用データセットとして使用するサンプル データを選択します。

2014、2015、2016 年の Sales データがあるため、ここではさまざまなテーブルが表示されます。最初のテーブルであるSales_2014を選択するだけです。

Sales_2014 データを選択したので、ハードコーディングされたファイルの場所を動的なファイルの場所に変更します。

詳細エディターに移動し、ファイルの場所に関連するものをすべて選択します。

次に、これを前に作成した FileLocation パラメーターに変更します。

次に、[完了] を押して、これらの変更を Sales_2014 クエリにロックします。

データ準備フォルダーの作成

Sales_2014 クエリの適用されたステップを見て、ナビゲーションステップをクリックします。これにより、 Sourceステップを参照していることがわかります。

また、Tableとして示されているSales_2014も参照しています。また、Data 列からデータを抽出していることも示しています。

さて、次にソースステップを見てみましょう。

「ソース」ステップでは、Excel ファイルに存在するすべての内容が表示されます。これらの座標を確認すると、 Sales_2014 が表示され、Kind の下にTableと表示されます。

データ列を参照すると、抽出されたTableオブジェクトが表示されます。

したがって、このクエリを複製して 2015 年のデータを得ることができます。これを行うには、Sales_2014 を右クリックし、[複製] を選択するだけです。

次に、ナビゲーション ステップに戻ります。

その年を 2014 年から 2015 年に変更します。

ここの「プロパティ」の下の名前も変更します。これを Sales_2015 と呼びます。

今回は Sales_2015 を複製します。

次に、ナビゲーション ステップに戻り、年を 2016 に変更します。

これで完全な売上データが手に入りました。ただし、これらのクエリをモデルにロードすべきではありません。それぞれを右クリックし、 [読み込みを有効にする] の選択を解除するだけです。

ここで、すべてのクエリを選択し、それらを 1 つのフォルダーに配置して、物事をより整理してみます。

このフォルダーをData Prepと呼びます。

日のオフセットを取得する

ここの「データ準備」のサンプル データは静的になります。これは、最初と最後の日付は決して変更されないことを意味します。

そこで、 Sales_2016に移動し、 Order Date列を降順で並べ替えて、このデータ セットから最後の日付を取得します。

このデータセットの最後の日付は2016 年 12 月 31 日です。

次に、新しいクエリを作成します。

次に、データ セットの最後の日付から今日までのDay Offsetを確認します。つまり、今日が何なのかを知る必要があります。これを行うには、関数DateTime.FixedLocalNowを使用します。これにより、作業しているマシンからの日付と時刻が得られます。

しかし、ここでは時間の部分は特に必要ありません。 この値の日付部分のみが必要です。

そこで、 Date.From を使用して日付を抽出します。

次に、その値からデータ セットの最後の日付を減算します。データは静的であるため、組み込みの日付値を使用できます。これが #date であり、2016 年 12 月 31 日を参照します。

次に、この差から数値を抽出する必要があります。そこで、 Number.From を追加し、括弧で閉じます。次に、Enter キーを押します。

これの名前をDayOffsetに変更します。

動的な日付範囲の設定

日付テーブルには動的な日付範囲も必要です。まず、Data Prep フォルダーを右クリックし、新しい空のクエリを作成します。

次に、 Date テーブルのStart Dateを操作します。

私は常に年の 1 日を開始日としたいと考えています。そこで、等号を付けて関数Date.StartOfyearを使用します。

これに値を与える必要があるため、 Date.AddDays を使用してそれをオフセットします。

次に、組み込みの日付値を再度使用し、データ セットの最初の日付 ( 2014 年 6 月 1 日) を使用します。

DayOffset で計算した日数も必要なので、それを追加します。

Enter キーを押すと、 2017 年 1 月 1 日が表示されます。これが日付テーブルの最初の日付になります。

その名前をfxStartDateに変更します。

これで、終了日を設定できるようになりました。開始日に使用したこの構文をコピーします。

新しい空のクエリを作成します。

次に、名前をfxEndDateに変更します。

次に、 Start Date の構文を貼り付けます。

StartOfyearの代わりに、これを EndOfyear に変更します。

また、データ セットの最後の日付である12 月 31 日を使用します。

Enter キーを押すと、2020 年 12 月 31 日が返されます。

単一の売上テーブルの作成

3 つのテーブルを 1 つの Sales テーブルに結合します。そのために、新しいクエリを作成します。

これをSalesテーブルと呼びます。

次に、Sales_2014、Sales_2015、Sales_2016 を1 つのテーブルに結合します。

リボンから実行できることはわかっていますが、数式バーで直接実行するオプションもあります。私は 2 番目のオプションを選択します。

Table.Combineから始めます。

Table.Combine にはテーブルのリストが必要です。そこで、中括弧であるリスト初期化子を追加し、 3 つのクエリに名前を付けます。その後 Enter を押してください。

注文日列の変換

必要な要素のほとんどが揃ったので、後で動的練習データセットを完成させるために使用する構文の重要な部分を生成します。

まず 2 つの数値列を選択します。配送地域インデックスと商品説明インデックスを使用できます。Shift キーまたは Ctrl キーを押して両方の列を選択できます。

[変換]タブで、 [絶対値]を選択します。

数式バーを見ると、Table.TransformColumns関数が表示されます。これが最終的な解決への鍵です。

この関数は変換リストのリストを受け取ります。ご覧のとおり、ここには 2 つのリストが互いに入れ子になっています。

列名をテキストとして参照します。そのため、ここでは第 1 セットで配達地域インデックスが参照されました。

次に、変換ステップを追加します。

[製品説明インデックス]列の別のリストでも同じことを行います。

次に、高度なエディターに進みます。

絶対値は必要ありません。したがって、最初にやることは日付をオフセットすることです。これを行うために、 fxUpdateDateという関数を作成します。現在の日付を使用するので、 cDateとして入力します。

次に、 cDateも使用するDate.AddDays関数を再度使用します。前に作成したDayOffsetも使用します。

fxUpdateDate が行うことは日付を取得することだけです。したがって、これをTable.TransformColumns関数に渡すと、各記録日のチェックが開始されます。

したがって、たとえば、ここで 1 行目を作業している場合、2014 年 6 月 1 日が cDate として考慮されます。

そして、関数が呼び出されるたびに、作成した DayOffset が追加されます。

これで、 Table.Transform ステップが[計算された絶対値]に表示されます。

まず名前を変更して、UpdateOrderDate と呼びます。

このUpdateOrderDateステップには、 Sourceと等しいTable.TransformColumnsが含まれます。

リストされる最初のテーブルは、配信地域インデックスです。しかし、それを変える必要はありません。そこで、参照されている最初の列をOrderDate列に変更します。

ここでは、変換は絶対値で行われるとも述べています。

これは望ましくないので、これをfxUpdateDateに変更します。

型も絶対値にはならないのでこの部分も変更する必要があります。

それを日付に変換します。

必要な変換は 1 つだけなので、2 番目のリストとそのセットに付属する変換ステップを削除します。

…つまり、これは OrderDate 列の 1 つの変換にすぎません。

計算された絶対値のステップの名前を変更したため、in ステートメントでもその名前を変更する必要があります。

UpdateOrderDateも表示されるようになりました。

したがって、OrderDate 列は、 Table.TransformColumns のレコードごとにこの関数を呼び出すようになります。

「注文日」列を見ると、これが更新されていることがわかります。

これを降順に並べ替えると、現在の日付として2020 年 7 月 7 日が表示されます。

実際には必要がないので、その並べ替えステップを削除します。現在の日付も更新されているかどうかを確認するためにこれを行っただけです。

M コードを使用したタイム インテリジェンスの適用

私のモデルはすべて売上データに関するものです。タイム インテリジェンスを実行するつもりなので、Dateテーブルが必要になります。

に行きます。M コード ショーケースでは、拡張日付テーブルのトピックを取り上げます。

ページの一番上までスクロールして、コード全体をコピーします。

モデルに戻り、新しい空のクエリを作成し、その M コードを に貼り付けます。「完了」を押します。

このクエリの名前をfxCalendarに変更します。

パラメーターには、StartDate に 1 月 1 日を、EndDate に 1 月 31 日を使用します。次に、「呼び出し」を押します。

テーブルが表示されたら、その名前をDatesに変更します。

サンプル データセットの最初の日付を確認し、それを DayOffset でオフセットすることで、開始日と終了日をすでに作成していることを思い出してください。したがって、ここでの fxStartDate は 2017 年 1 月 1 日として表示されます。

fxEndDate に関しては、2020 年 12 月 31 日が返されます。

Datesテーブルに戻って、そこでその Source ステップを更新することもできます。

パラメータが呼び出された順序でリストされます。最初のパラメータには開始日を表示する必要があります。

これをfxStartDateに変更します。

2 番目のパラメータも置き換えます。

fxEndDateを入れます。

Enter キーを押すと、変換が完了します。これで、常に最新の状態に保たれる完全に最新の練習用データセットが手に入りました。

[閉じて適用]をクリックして、すべての手順をロックします。

LuckyTemplates で動的日付クエリ テーブルを作成する: クエリ エディターのチュートリアル拡張日付テーブルでのオフセット関数を使用した
Power Query 日付テーブルの動的な開始日と終了日の設定

結論

前に述べたように、LuckyTemplates に関しては、独自の動的な練習用データセットがあれば間違いなくゲームがレベルアップします。現実のシナリオに取り組んでいるかのように、スキルと知識を練習することができます。

特に使用している練習データセットの開始日と終了日を動的にする方法について詳しく知りたい場合は、LuckyTemplates フォーラムをチェックしてください。このことやその他の関連トピックについて多くの会話が行われています。あなたにとって効果的な他のアプローチも見つかるかもしれません。

ではごきげんよう、

メリッサ