Excel の複数のシートを LuckyTemplates に追加する

このチュートリアルでは、複数のシートを含む Excel ファイルを 1 つの LuckyTemplates テーブルにインポートして開く方法について説明します。

このデータ Excel ファイルはこのチュートリアルで使用されます。

このファイルには同じレイアウトのデータが 60 枚含まれています。

このチュートリアルの目的は、すべての Excel シートを LuckyTemplates に読み込み、結合情報を 1 つのテーブルとして取得することです。セキュリティ ティッカーを取得し、それを別の列に追加する必要があります。次に、UI を利用して必要なコードを生成する単一のテーブルにそれらすべてを追加します。

目次

• Excel シートを LuckyTemplates にインポートする
• Excel から LuckyTemplates へのセキュリティ ティッカーの導入
• 空の行の削除
• クエリの複製
• 列へのデータ型の割り当て
• Excel から LuckyTemplates にセキュリティ ティッカー列を追加する
• 結合クエリ用のカスタム列の作成
• 結論

Excel シートを LuckyTemplates にインポートする

最初に行うことは、Power Query エディターを開き、 [パラメーターの管理]オプションを使用してファイルの場所を保存するパラメーターを作成することです。

「パラメータの管理」ボックス内で「新規」をクリックし、パラメータ名として「FileLocation」と入力します。次に、[データ タイプ] を[テキスト]に設定し、[推奨値] を[任意の値]に設定します。Excel ファイルのファイル パスをコピーし、[現在の値] フィールドに貼り付けます。

[OK]を押した後、[新しいソース]をクリックし、[Excel ワークブック]を選択します。

次に、Excel ファイルを選択して開きます。完了すると、ナビゲーター画面が開き、Excel ファイル内のすべてのシートがリストされます。

最初のシートを選択し、「OK」を押します。その後、Power Query エディター内にテーブルが表示されます。

次に行うことは、ハードコーディングされたファイル パスを file パラメーターに変更することです。Advanced Editor ウィンドウを開き、ソース コード内のファイル パスをFileLocationに変更します。

境界またはデータ範囲はPower Queryに取り込む前に定義されるため、データを Excel テーブルに保存することをお勧めします。ファイルにはシートが含まれておりテーブルが含まれていないため、空の列や空の行が持ち込まれるリスクがあるため、それを整理する必要があります。

Excel から LuckyTemplates へのセキュリティ ティッカーの導入

シートのレイアウトが固定されていることを知っておくと、特にユーザー インターフェイスを最大化してコードを生成する場合に、ソリューションを構築するのに役立ちます。たとえば、要件の 1 つは、セキュリティ ティッカーを含む列を追加することです。データを見るとティッカーが表示されます。

ユーザー インターフェイスは、ティッカーの値を抽出するのに役立ちます。セルを右クリックし、[新しいクエリとして追加]を選択します。

数式バーでは、テーブル名の後に中かっこで囲まれた 0 から始まる行番号が続いていることがわかります。中括弧は位置インデックス演算子と呼ばれます。また、フィールド アクセス演算子と呼ばれる角括弧で囲まれたフィールド名も表示されます。

これらのコードを使用して、値を抽出できるようになります。Bond テーブルに戻り、Bond データを取得します。まず、上部88行を削除します。[行の削除]をクリックし、[上位行の削除]を選択します。

次に、「行数」フィールドに8 を入力し、「OK」を押します。

次に、「最初の行をヘッダーとして使用」をクリックしてヘッダーを設定します。

完了すると、Bond テーブルは次のようになります。

ヘッダーのすぐ下に、列の品質を表す細い線が表示されます。そこから、列にかなりの数の空白があることがわかります。これは、ファイルに多くの空の行が取り込まれたことを意味します。

空の行の削除

空の行を削除するには、「行の削除」をクリックし、「空白行の削除」を選択します。

この変換により、次の構文が生成されます。

Record.FieldValues は、テーブル内の現在の行からすべての値をリストとして取得します。List.RemoveMatchingItems は、 2 番目のリストに一致する最初のリストの値をすべて削除します。2 番目のリストには、空のテキスト文字列または null のみが含まれます。これらは、最初のリストから除外される値です。

すべての空のテキスト文字列と null がレコード フィールド値を持つリストから削除されている場合、リストは空になるはずで、List.IsEmpty はTrueと評価されます。その後、Table.SelectRows はTrue を保持します。

空白行だけが含まれるテーブルが完成することは避けてください。そのため、キーワードnot がList.IsEmptyの前に追加されます。これにより、空白以外の行を含むテーブルが返されます。

空白行のほかに、空白列も削除する必要があります。その前に、列を削除したときに Power Query が生成するものを見てみましょう。4 番目の列を選択し、[列の削除]をクリックします。

変換を実行すると、数式バーにこの構文が表示されます。

これは、Table.RemoveColumns関数を呼び出し、[適用されたステップ] ペインの前のステップを参照して最初の引数として渡します。変換ではさらに、削除する列の列名を含むリストを渡します。

クエリの複製

次に、クエリを複製し、[適用されたステップ] ペインで [ヘッダーの昇格] ステップを選択します。次に、そのステップを右クリックし、[終了するまで削除]を選択します。

位置インデックス演算子を使用すると、中かっこのセット内で 0 から始まる行番号を渡すことができることに注意してください。したがって、数式バーの 2 つの中括弧内に0を入力して、最初の行のみを保持します。

完了すると、Bond テーブルは次のようになります。

そこから、「空白行の削除」ステップで作成されたロジックの一部を再利用して、レコードをリストに変換し、NULL を削除します。Bond クエリに戻り、Remove Blank Rows ステップを選択します。次に、このMコードをコピーします。

複製したクエリに戻り、数式バーにコードを貼り付けます。次に、いくつかの文字列を配置して数式を次のようにします。

これで、保持したい列名のリストが作成されました。クエリの名前をColumn Namesに変更します。

次に、Bond クエリに戻ります。保持したい列をすべて含むクエリを作成したので、Table.RemoveColumns 関数で除外したい列をすべてリストする必要があります。

「空白行の削除」ステップからコピーしたものと同じ構文を使用して{Column4}を変更します。{"", null}もColumnNamesに変更します。

次に、Bond テーブルから実際の列名のリストを渡す必要があります。Record.FieldValues(_)をTable.ColumnNames()に変更します。括弧内に#”Removed Blank Rows”を入力してテーブル参照引数を渡します。

列へのデータ型の割り当て

次に行うことは、列に適切なデータ型を割り当てることです。「日付」列では、ヘッダーの横にあるアイコンをクリックして「日付」を選択します。

PX_LAST 列と YLD_YTM_MID 列には、10 進数データ型を選択します。

これら 3 つのクエリにより、 Excelファイル内のすべてのシートに対応するソリューションを設計するために使用できるすべての構成要素が作成されました。そのためには、Bond クエリを複製し、[適用されたステップ] ペインでソース ステップを除くすべてのステップを削除する必要があります。

Excel から LuckyTemplates にセキュリティ ティッカー列を追加する

[ソース] ステップでは、Excel ファイル内のすべてのデータを確認できます。シートごとに個別のクエリを作成する代わりに、Bond クエリを使用して、データ列のネストされたテーブルを変換できます。

まず、セキュリティティッカーを追加します。表のセル内の空白をクリックすると、その表の内容のプレビューが表示されます。

セキュリティ ティッカーを取得するには、 ステートメントを使用してロジックを作成する必要があります。Column2 クエリに移動し、数式バーからセキュリティ ティッカーのアドレスをコピーします。

次に、Bond クエリに戻り、カスタム列を追加します。

列に Security Ticker という名前を付け、次の M コードを記述します。

この数式には、Column1 で Security という単語が見つかった場合、それに隣接する Column2 のセルの値を提供するというIFステートメントが含まれています。それ以外の場合は、null が提供されます。

[OK] を押すと、セキュリティ ティッカーを含む新しい列がテーブルに追加されます。

[セキュリティ ティッカー] 列のドロップダウン ボタンをクリックし、[ヌル] の選択を解除して、列内のすべてのヌルを削除します。

その後、すべてのシートからのボンドのすべての情報が残ります。必要なのは、Bond(2) クエリで実行された変換を繰り返し、それらを Data 列のネスト���れたテーブルに適用することだけです。

結合クエリ用のカスタム列の作成

Bond(2) クエリに移動し、詳細エディター ウィンドウを開いて、次のコードをコピーします。

次に、Bond クエリに戻り、別のカスタム列を作成します。複数のステップにわたって複数の変換を適用する必要があるため、 letステートメントを使用する必要があります。そこで、「let」と入力し、アドバンストエディターからコードを貼り付けます。

次に、Bond_Sheet をDataに変更して、Data 列のテーブルを変換します。

OK を押すと、テーブルに新しい列が追加されます。

その列の任意のセル内の空白をクリックすると、Bond(2) クエリからのデータが表示されます。

必要なのは、Bond クエリの最後の 2 列だけです。したがって、セキュリティ ティッカー列とカスタム列の両方を選択し、[列の削除] リボンから[他の列の削除]をクリックします。

[カスタム] 列を展開し、[元の名前をプレフィックスとして使用] ボックスのチェックを外します。

完了したら、テーブルを検証します。テーブルの下で、「データセット全体に基づく列プロファイリング」オプションを選択します。

この変換後、データ列でエラーが発生します。

したがって、[データ] 列をクリックし、[行を保持] リボンで [エラーを保持] を選択して、エラーを調べます。

[データ] 列内のエラー値をクリックすると、以下のエラー メッセージが表示されます。

問題を解決するには、まず「Kept Errors」ステップを削除します。[データ] 列をクリックし、[行の削除] リボンから[エラーの削除]を選択します。

次に、列プロファイリング オプションを上位 1000 行に戻します。以上です！

結論

このユーザー インターフェイス主導のソリューションは、Excel ファイルから LuckyTemplates に複数のシートを追加するのに役立ちます。このソリューションでは、60 個の個別のクエリを作成してすべての変換を繰り返し実行する代わりに、すべての変換を実行する単一のクエリを作成できます。このソリューションを最大限に活用して、優れたデータ レポートを作成します。

メリッサ