Microsoft Excel で無料の Power Query アドインのツールの使用を既に開始している場合は、非常に専門的ではあるものの、ソース データへのリンクが頻繁に切断されるという非常に頻繁で煩わしい問題にすぐに遭遇します。 問題の本質は、クエリで外部ファイルまたはフォルダーを参照する場合、Power Query がそれらへの絶対パスをクエリ テキストにハードコーディングすることです。 あなたのコンピューターではすべてが正常に機能しますが、同僚に要求を含むファイルを送信することにした場合、彼らはがっかりするでしょう。 コンピューター上のソース データへのパスが異なるため、クエリは機能しません。
そのような状況で何をすべきか？ このケースを次の例で詳しく見てみましょう。
問題の定式化
フォルダにあるとします E:販売レポート ファイルがあります トップ 100 製品.xlsは、当社の企業データベースまたは ERP システム (1C、SAP など) からアップロードされたものです。このファイルには、最も人気のある商品アイテムに関する情報が含まれており、内部は次のようになっています。
Excel でこの形式で作業することはほとんど不可能であることは、おそらくすぐに明らかです。空の行からデータのある行、結合されたセル、余分な列、複数レベルのヘッダーなどが干渉します。
したがって、同じフォルダー内のこのファイルの隣に、別の新しいファイルを作成します ハンドラ.xlsxで、ソース アップロード ファイルから醜いデータをロードする Power Query クエリを作成します。 トップ 100 製品.xls、それらを順番に並べます。
外部ファイルへのリクエストの実行
ファイルを開く ハンドラ.xlsx、タブで選択 且つ Command データの取得 – ファイルから – Excel ワークブックから (データ — データの取得 — ファイルから — Excel から)、次にソース ファイルの場所と必要なシートを指定します。 選択したデータが Power Query エディターに読み込まれます。
それらを通常に戻しましょう。
- 空行を削除する ホーム — 行を削除する — 空行を削除する (ホーム — 行の削除 — 空の行の削除).
- 不要な上4行を削除 ホーム — 行の削除 — 上位行の削除 (ホーム — 行を削除 — 上の行を削除).
- ボタンで最初の行をテーブル ヘッダーに上げます 最初の行をヘッダーとして使用する タブ ホーム (ホーム — 最初の行をヘッダーとして使用).
- コマンドを使用して、XNUMX 列目の製品名から XNUMX 桁の記事を区切ります。 分割列 タブ 変換 (変換 — 列を分割).
- 見やすくするために、不要な列を削除し、残りの列の見出しの名前を変更します。
その結果、次のような、より快適な画像が得られるはずです。
この高貴なテーブルをファイルのシートにアップロードする必要があります ハンドラ.xlsx チーム 閉じてダウンロード (ホーム — クローズ & ロード) タブ ホーム:
リクエスト内のファイルへのパスを見つける
では、Power Query に組み込まれた内部言語 (簡潔な名前 "M") で、クエリが "内部" でどのように見えるかを見てみましょう。 これを行うには、右ペインでダブルクリックしてクエリに戻ります リクエストと接続 そしてタブで レビュー 選ぶ 高度なエディタ (表示 — 詳細エディター):
開いたウィンドウの XNUMX 行目に、元のアップロード ファイルへのハードコードされたパスがすぐに表示されます。 このテキスト文字列をパラメーター、変数、またはこのパスが事前に記述されている Excel シート セルへのリンクに置き換えることができれば、後で簡単に変更できます。
ファイル パスを使用してスマート テーブルを追加する
とりあえず Power Query を閉じて、ファイルに戻りましょう ハンドラ.xlsx. 新しい空のシートを追加して、その上に小さな「スマート」テーブルを作成しましょう。ソース データ ファイルへのフル パスが書き込まれる唯一のセルです。
通常の範囲からスマート テーブルを作成するには、キーボード ショートカットを使用できます Ctrlキー+T またはボタン 表としてフォーマットする タブ ホーム (ホーム — テーブルとしてフォーマット). 列見出し (セル A1) は、絶対に何でもかまいません。 また、わかりやすくするために、テーブルに名前を付けたことにも注意してください 計測パラメータ タブ コンストラクタ （設計）.
もちろん、エクスプローラーからパスをコピーしたり、手動で入力したりすることは特に難しいことではありませんが、人的要因を最小限に抑え、可能であればパスを自動的に決定することをお勧めします。 これは、標準の Excel ワークシート関数を使用して実装できます。 CELL （細胞）これは、現在のファイルへのパスを含む、引数として指定されたセルに関する有用な情報を提供できます。
ソースデータファイルが常にプロセッサーと同じフォルダーにあると仮定すると、必要なパスは次の式で形成できます。
=LEFT(CELL("ファイル名");FIND("[";CELL("ファイル名"))-1)&"トップ 100 製品.xls"
または英語版：
=LEFT(CELL(«ファイル名»);FIND(«[«;CELL(«ファイル名»))-1)&»Топ-100 товаров.xls»
…関数はどこにありますか レブシム （左） 完全なリンクから左角かっこ (つまり、現在のフォルダーへのパス) までのテキストを取得し、ソース データ ファイルの名前と拡張子を貼り付けます。
クエリでパスをパラメータ化する
最後で最も重要なタッチが残っています – リクエストにソースファイルへのパスを書き込むことです トップ 100 製品.xls、作成した「スマート」テーブルのセル A2 を参照 計測パラメータ.
これを行うには、Power Query クエリに戻ってもう一度開きます。 高度なエディタ タブ レビュー (表示 — 詳細エディター). 引用符で囲まれたテキスト文字列パスの代わりに 「E:販売レポートTop 100 products.xlsx」 次の構造を紹介しましょう。
Excel.CurrentWorkbook(){[Name="設定"]}[内容]0 {}[ソースデータへのパス]
それが何で構成されているか見てみましょう：
- Excel.CurrentWorkbook() 現在のファイルの内容にアクセスするための M 言語の関数です。
- {[Name="設定"]}[内容] – これは前の関数の改良パラメータであり、「スマート」テーブルの内容を取得したいことを示します 計測パラメータ
- [ソースデータへのパス] テーブル内の列の名前です 計測パラメータ私たちが参照する
- 0 {} テーブルの行番号です 計測パラメータそこからデータを取得します。 上限はカウントされず、番号は XNUMX からではなく XNUMX から始まります。
実際、それだけです。
クリックする必要があります 終了 リクエストがどのように機能するかを確認します。 現在、両方のファイルを含むフォルダー全体を別の PC に送信すると、要求は引き続き機能し、データへのパスが自動的に決定されます。
- Power Query とは何ですか? Microsoft Excel で作業するときに Power Query が必要な理由
- フローティング テキスト スニペットを Power Query にインポートする方法
- Power Query を使用して XNUMXD クロス集計をフラット テーブルに再設計する