Power Query を使用してカレンダーのレイアウトを表形式に変換する

このチュートリアルでは、LuckyTemplates の Power Query を使用して、複数のカレンダー タイプのレイアウトを表形式に変換する方法を学習します。フォルダー内の単一のファイルではなく、複数のファイルを変換する方法を学習します。このチュートリアルの完全なビデオは、このブログの下部でご覧いただけます。

このチュートリアルでは、データのギャップを埋める方法と、LuckyTemplates でデータを収集する方法についても説明します。

目次

• Power Query でのファイルとレイアウトの準備
• Power Query での計算の定式化
• 日付のギャップを埋める
• 分割されたテーブルのグループ化
• 結論

Power Query でのファイルとレイアウトの準備

これは、水平方向と垂直方向に配置されたファイルと週の典型的なレイアウトです。行数が異なるため、ファイルごとに行数が異なる場合があります。

次に、Power Query に移動してファイルを取得します。「新しいソース」を選択し、「詳細」をクリックします。次に、「すべて」をクリックして「フォルダー」を選択します。

「パラメータ」を選択し、「OK」を押します。

フォルダー内のファイルが表示されます。すべてのファイルを 1 つの結果クエリにまとめて取得する必要があります。「結合と変換」をクリックすると、データがロードされ、多数のクエリが作成されます。

次に、フォルダー内の任意のファイルをサンプル ファイルとして選択し、開始したい項目を選択します。

Query1 を見ると、File1 と File2 の両方のデータが表示されます。

Power Query での計算の定式化

Query1 のデータの形式は依然として正しくありません。これは、サンプル ファイルの変換クエリを通じて変更できます。

そのクエリの形式を表形式に変更すると、フォルダー内のすべてのファイルの設計図として機能します。

最初の 2 行と Column1 を削除します。

次に、テーブルの内側の隙間を埋めます。「変換」タブに移動し、「転置」を選択します。日付を含む列を選択し、ヘッダーをクリックします。「塗りつぶし」をクリックし、「下へ」を選択します。

数式バーの日付の列は、週ごとに行数が異なる場合、結果はそれらの列に表示されないことを意味します。したがって、「適用されたステップ」セクションの「Filled Down」ステップを削除します。

テーブルの各グループは、日付を含む列から始まります。表形式に戻すには、基本的な操作を実行する必要があります。

数式バーでf(x)を押して、手動ステップを追加します。次に、「 Table.ToColumns 」と入力します。これにより、各列がリストを含むリストに変わります。

リストをテーブルに変換し、インデックス番号を追加します。0または1から変更できます。

日付のギャップを埋める

日付にはまだ空白があり、記入する必要があります。ただし、fill down 操作はリストに対しては機能しません。このリストをテーブルに変換する必要があります。

[インデックス列] を選択し、[グループ化] をクリックします。列名を変更するには、Count を ToTable に置き換えます。RowCount を FromColumns に変更し、リストが配置されている Column1 をポイントします。次に、それを型テーブルに変換します。

リストが 1 列だけのテーブルに変わっていることがわかります。

ここで、各テーブルに対してフィルダウン操作を実行します。まず、カスタム列を追加し、FillDownという名前を付けます。

テーブルの最初の列の最初の値にアクセスする必要があるため、数式にはIFを使用します。テーブルを保持する ToTable 列を参照します。参照する列として Column1 を入力します。次に、それらの間に null を使用して、リストから最初の値を取得します。

次に、それが日付であるかどうかを確認します。存在する場合は、その列を Table.Filldown で埋めます。その関数内に、ToTable と Column1 を入力します。ただし、日付ではない場合は、ToTable と入力してテーブル全体を返します。

これにより、テーブルが埋められた新しい列が作成されます。

列の日付が配置されている場所を見つけて、それらを別のテーブルに分割します。これを行うには、テーブルを列のリストに変換する必要があります。

数式バーの Table.FillDown 関数と Else 関数の後で、Column1 を参照します。

これにより、テーブルが日付のセットを含むリストに変わります。

次に、インデックス列を更新して、リストをテーブルに戻します。分割場所は日付列の位置であることに注意してください。そこで、カスタム列を追加し、Index2という名前を付けます。次に、リストに日付が含まれているかどうかを確認します。

IFを使用して FillDown を入力します。リストの最初の値を確認するだけで済みます。日付の場合は Index を返します。そうでない場合は、nullを入力します。次に、その列を埋めていきます。

分割されたテーブルのグループ化

Index2 列で Group by を使用します。これにより、同じ Index2 値を持つテーブルが 1 つにグループ化されます。

行をカウントするつもりはありません。したがって、RowCount を FromColumns に変更し、FillDown 列を追加します。次に、タイプをテーブルに変更します。

テーブルをクリックすると、そのテーブル内の列が表示されます。

次に、すべての個別のテーブルを 1 つのテーブルに追加する必要があります。手動ステップを作成し、Table.Combine を使用します。次に、その中の Count 列を参照します。

列 1 と列 2 を選択します。「変換」をクリックし、「他の列のピボットを解除」を選択します。すると 4 つの列が表示されます。

[属性] 列は必要ないので削除します。次に、列の名前をDate、Movements、およびNameに変更します。Date 列のデータ型を Date に変更し、Name 列のデータ型を Text に変更します。

これで、変換サンプル ファイルへの変換が更新されました。

Query1 に移動すると、テーブルの Column1 が見つからないことがわかります。したがって、「適用されたステップ」セクションの「変更されたタイプ」ステップを削除します。

最後に、列のデータ型を変更します。Date 列のデータ型を Date に設定します。次に、Movements 列と Name 列のデータ型を Text に設定します。

これで、データ モデルの形式が更新され、表形式に変更されました。

LuckyTemplates カスタム カレンダー: 月ごとの変化の計算 – 445 カレンダー
LuckyTemplates のカスタム条件付き書式設定テクニック
LuckyTemplates で複数の日付を操作する方法

結論

データ レポートでは、カレンダー レイアウトを表形式に変換して、日付テーブルのギャップを埋める必要があります。

LuckyTemplates の Power Query を使用して、複数の列を変換できます。これにより、レポートに正確で貴重な洞察が得られます。

ではごきげんよう、

メリッサ