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

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

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

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

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

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

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

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

目次

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

空の行の削除

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

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

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

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

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

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

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

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

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

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

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

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

クエリの複製

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

結論

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

メリッサ


Power Automate の文字列関数: Substring と IndexOf

Power Automate の文字列関数: Substring と IndexOf

Microsoft フローで使用できる 2 つの複雑な Power Automate String 関数、substring 関数とindexOf 関数を簡単に学習します。

LuckyTemplates でビジュアル ツールチップを作成する

LuckyTemplates でビジュアル ツールチップを作成する

LuckyTemplates ツールチップを使用すると、より多くの情報を 1 つのレポート ページに圧縮できます。効果的な視覚化の手法を学ぶことができます。

Power Automate で HTTP 要求を行う

Power Automate で HTTP 要求を行う

Power Automate で HTTP 要求を作成し、データを受信する方法を学んでいます。

LuckyTemplates で日付テーブルを作成する方法

LuckyTemplates で日付テーブルを作成する方法

LuckyTemplates で簡単に日付テーブルを作成する方法について学びましょう。データの分析と視覚化のための効果的なツールとして活用できます。

2 つの方法による SharePoint 列の検証

2 つの方法による SharePoint 列の検証

SharePoint 列の検証の数式を使用して、ユーザーからの入力を制限および検証する方法を学びます。

SharePoint リストを Excel または CSV ファイルにエクスポート

SharePoint リストを Excel または CSV ファイルにエクスポート

SharePoint リストを Excel ファイルおよび CSV ファイルにエクスポートする方法を学び、さまざまな状況に最適なエクスポート方法を決定できるようにします。

Power Automate のオンプレミス データ ゲートウェイ

Power Automate のオンプレミス データ ゲートウェイ

ユーザーがコンピューターから離れているときに、オンプレミス データ ゲートウェイを使用して Power Automate がデスクトップ アプリケーションにアクセスできるようにする方法を説明します。

DAX 数式での LASTNONBLANK の使用

DAX 数式での LASTNONBLANK の使用

DAX 数式で LASTNONBLANK 関数を使用して、データ分析の深い洞察を得る方法を学びます。

CROSSJOIN 関数の使用方法 – LuckyTemplates および DAX チュートリアル

CROSSJOIN 関数の使用方法 – LuckyTemplates および DAX チュートリアル

LuckyTemplates で予算分析とレポートを実行しながら、CROSSJOIN 関数を使用して 2 つのデータ テーブルをバインドする方法を学びます。

TREATAS 関数を使用して LuckyTemplates で仮想リレーションシップを作成する

TREATAS 関数を使用して LuckyTemplates で仮想リレーションシップを作成する

このチュートリアルでは、LuckyTemplates TREATAS を使用して数式内に仮想リレーションシップを作成する方法を説明します。