クエリ エディターを使用して LuckyTemplates でカレンダー タイプのレイアウトを表形式に変換する

私たちが持っているデータは、正しく整理されていないと分析が困難になる可能性があります。このチュートリアルでは、縦横の繰り返しデータを表形式に変換する方法を説明します。これは、LuckyTemplates を使用して分析を容易にするために行われます。このチュートリアルの完全なビデオは、このブログの下部でご覧いただけます。

メンバーの質問に答えるために作成されました  。メンバーは、データをカレンダー形式のレイアウトから表形式に改造する方法を求めました。

始める前に、まず手持ちのデータを調べてみましょう。

ここでは、日が横方向に、週が縦方向に配置されたデータ レイアウトがあります。このタイプの配置は、データを簡単に理解できる方法であるため、ほとんどの人にとって馴染みのあるものです。

ただし、このタイプのレイアウトは考慮すべき列と行が多数あるため、分析が難しくなります。

そうは言っても、データを適切な表形式に変換して、日付の列、移動の列、名前の列を作成します。

目次

• 適切な表形式のためのデータのクリーンアップ
• テーブルを壊す
• 適切な表形式のための新しいテーブルのリストの特定
• テーブルに属する値の決定
• 適切な表形式のためのデータのグループ化
• 個別のテーブルを追加する
• 結論

適切な表形式のためのデータのクリーンアップ

作業を簡単にするために、 Power Query エディター内でデータを変換しましょう。ただし、その前に、数式バーが画面に表示されていることを確認しましょう。そうでない場合は、[表示] タブに移動し、数式バーの横にあるボックスをオンにします。

最初のステップは、データ内の不要な値を削除することです。データの最初の列と上の 2 行は実際には必要ないので、削除しましょう。これを行うには、[行の削除] タブをクリックし、[上位行の削除] をクリックして 2 を入力し、[OK] を押します。

最初の列を削除するには、[列の選択] タブに移動し、列 1 の横にあるボックスのチェックを外して列 1 の選択を解除します。

また、インデックス 14 から 16 までの行は完全に NULL 値だけで埋められているため、これらの行も削除しましょう。再度、「行の削除」タブに移動し、「空白行の削除」をクリックします。

もう 1 つの問題は、日付値が毎回列をスキップすることです。以下で強調表示されているように、日付の後には常に null 値が続きます。これは上の行だけでなく、下の行でも同様に発生します。

これらのギャップを埋めるには、まず [変換]タブに移動して [転置] ボタンをクリックしてデータ テーブルを転置しましょう。

アクションを実行した後、テーブルは横にひっくり返ったように見えるはずです。

次に、日付を含むすべての列を選択し、列ヘッダーを右クリックして、[下へ埋める] を選択します。

これにより、日付が複製され、選択した列のギャップが埋められます。

テーブルを壊す

現在のデータを見ると、互いに隣接する 4 つのテーブルがあることがわかります。列 1 ～ 7 が 1 つのテーブルを形成し、列 8 ～ 13 が別のテーブルを形成する、というようになります。

これらを 1 つのテーブルに追加するには、基本的なアンピボット操作を実行して、テーブルを適切な表形式に変換する必要があります。しかし、テーブルは互いに隣接しているため、単純に行うことはできません。このテーブルを分解して、小さなテーブルに再びつなぎ合わせなければなりません。

これを行うために、このチュートリアルの最初の数式を作成します。[fx]ボタンをクリックして、使用する数式を追加しましょう。Table.ToColumnsオペレーションを使用します。これにより、テーブルから列値のネストされたリストのリストが作成されます。各列がリストに変換されます。

こんな感じで式を立ててみました。

この数式は、以前に持っていた列のリストを作成します。リストの最初の値には列 1 が含まれ、2 番目の値には列 2 が含まれます。

適切な表形式のための新しいテーブルのリストの特定

ここで、リスト上のどの値に日付値が含まれているかを特定する必要があります。これらの日付がどこにあるかがわかれば、個別のテーブルの開始位置と終了位置を決定できます。

これを行うには、まず左上隅にある [テーブルへ] ボタンをクリックして、リストをテーブルに変換し直します。

次に、その隣にインデックス列を追加します。

次に、インデックス列の隣に別の列を追加します。この列は、Column1 のどの値に日付値が含まれているかを判断するのに役立ちます。この列に Index2 という名前を付けます。

これを行うには、日付が含まれている場合に Column1 のインデックス値を返す数式を Index2 に組み込みます。

を使用して、Column1 を参照します。括弧は演算子として機能します。これらの括弧の間に 0 を追加すると、リストから最初の値を取得することになります。次に、それが日付であるかどうかを判断します。そうであれば、数式がインデックス番号を返すようにしたいと思います。そうでない場合は、値をまったく返さないようにします。

Column1 の最初の値には日付が含まれているため、Index2 の最初の値にはインデックス番号 0 が含まれている必要があります。また、Column1 の 2 番目の値には日付が含まれていないため、Index2 の 2 番目の値には null 値のみが含まれている必要があります。

これが Index2 の式の設定方法です。数式に構文エラーがないことを確認してください。

これを実行すると、Index2 列には、日付を含む Column1 の値のインデックスが設定されるはずです。設定した式の結果として、いくつかの null 値も含まれます。

テーブルに属する値の決定

ここで、Index2 内のこれらのインデックス値は、所有する個別のテーブルの開始と終了を示します。つまり、0 は最初のテーブルの開始をマークし、7 は最初のテーブルの終了をマークし、2 番目のテーブルの開始をマークする、というようになります。

Column1 のどの値が同じテーブルに属しているかを識別しやすくするために、新しい列にfill downアクションを適用してみましょう。

アクションの実行後、NULL 値は 0、7、13、および 20 に置き換えられるはずです。これらの番号は、それらが同じテーブルに属していることを示します。つまり、0 が 1 つのテーブルを形成し、7 が別のテーブルを形成する、というようになります。

適切な表形式のためのデータのグループ化

Column1 のどの値が同じテーブルに属するかはすでにわかっているので、それらをグループ化しましょう。これを行うには、Group By 操作を使用します。

これにより、Index2 列には 4 つの値のみが含まれるようになります。Index2 列の横に Count という名前の別の列があることに注意してください。この列は、Index2 列の各値の行数をカウントします��

しかし、これは私たちが本当にやりたいことではありません。私たちがしなければならないのは、持っている列を使用して新しいテーブルを作成することです。

これを行うには、 Table.FromColumnsオペレーションを使用し、リスト値を含む列 (Column1) を参照し、戻り値の型をテーブルに変更します。

数式内で正しい列を参照していることを確認することが重要です。Column1 にリスト値が本当に含まれているかどうかを確認します。

これにより、Column1 の Index2 値が 0 のすべての値が 1 つのテーブルに結合され、Index2 値が 7 の値が別のテーブルに結合されます。

すべてのレコードを値 0 にチェックし、Column1 からリスト値を取得しました。最後に、この M コードを使用してこれらのリストを結合して 1 つのテーブルを作成しました。

最初の 7 列を取得して 1 つのテーブルに結合し、次のいくつかの列を取得して別のテーブルに結合する、という作業を繰り返しました。

個別のテーブルを追加する

すでにテーブルをグループ化したので、別のステップを追加してすべてのテーブルを追加してみましょう。もう一度[fx]ボタンを押して、新しい数式を追加しましょう。

Table.Combine操作は、テーブルのリストをマージし、それらから 1 つの単一テーブルを作成します。ここでは、テーブルのリストを含む列、つまり Count 列を参照します。

これで、すべてのネストされたテーブルが 1 つの大きな単一テーブルに追加されたテーブルが完成しました。

すべてのテーブルを追加したら、データを適切な表形式に変換する最後のいくつかの手順を実行できます。テーブルの最初の 2 つの列を選択し、[変換] タブの[他の列のピボットを解除]ボタンをクリックします。

その後、属性列は実際には必要ないので削除します。[列の選択]に移動し、属性列の選択を解除します。

次に、最後に、すべての列の正しいタイプを設定し、適切にラベルを付けます。

各列の列ヘッダーをダブルクリックして、それに応じて名前を変更します。

最初の列には日付値が含まれているため、その型を日付に変更します。

次に、2 番目と 3 番目の列を選択し、その種類をテキストに設定します。

すべて完了！これは、適切な表形式がどのように見えるかです。

結論

このチュートリアルでは、カレンダー タイプのレイアウトで表示されていた一部のデータを適切な表形式に変換しました。まずデータをクリーンアップし、それを小さなテーブルに分割し、同じテーブルに属するテーブルをグループ化し、最後にそれらをすべて追加しました。

このチュートリアルでは、分析を容易にするためにデータを適切にモデル化する方法について多くの知識を提供します。

ここで使用した概念を理解することは、他の多くの方法にも適用できるため、非常に重要です。

概念がまだよくわからない場合は、以下のリンクを確認すると役立つかもしれません。

楽しんで取り組んでください。

メリッサ