ほぼすべての Power Query トレーニングで、作成したクエリを更新する方法を説明し、更新時に新しいデータが古いデータに置き換わる様子を人々が目にすると、リスナーの XNUMX 人が私に尋ねます。また、どこかに保存され、更新履歴全体が表示されましたか?
このアイデアは新しいものではなく、それに対する標準的な答えは「いいえ」です。Power Query は既定で、古いデータを新しいデータに置き換えるように構成されています (これはほとんどの場合に必要です)。 ただし、本当に必要な場合は、この制限を回避できます。 後で見るように、その方法は非常に簡単です。
次の例を考えてみましょう。
入力データとしてクライアントからのファイルがあると仮定しましょう (それを呼び出しましょう、たとえば、 ソース) という名前の「スマートな」動的テーブルの形式で購入したい製品のリスト 申し込み:
別のファイルで（類推して呼びましょう 受信機) ソースから製品を含むテーブルをインポートするための簡単なクエリを作成します。 データ – データの取得 – ファイルから – Excel ワークブックから (データ — データの取得 — ファイルから — Excel ワークブックから) 結果のテーブルをシートにアップロードします。
将来、クライアントが自分のファイルの注文を変更することを決定した場合 ソース、次にリクエストを更新した後（右クリックまたは データ – すべて更新) ファイルに新しいデータが表示されます 受信機 — すべて標準。
ここで、更新時に古いデータが新しいデータに置き換えられるのではなく、新しいデータが古いデータに追加され、日時が追加されることを確認して、これらの特定の変更がいつ行われたかを確認できるようにします。作る。
ステップ 1. 元のクエリへの日時の追加
リクエストを開きましょう 申し込みからデータをインポートする ソース、更新の日時を含む列を追加します。 これを行うには、ボタンを使用できます カスタム列 タブ 列の追加 (列を追加 — カスタム列)、次に関数を入力します DateTime.LocalNow – 関数のアナログ TDATA （今） マイクロソフト エクセル:
をクリックした後 OK 次のようなきれいな列になるはずです (列ヘッダーのアイコンを使用して日時形式を設定することを忘れないでください)。
必要に応じて、この列のシートにアップロードされたプレートについて、精度を高めるために日時形式を秒で設定できます (標準形式にコロンと「ss」を追加する必要があります)。
ステップ 2: 古いデータのクエリ
ここで、更新前に古いデータを保存するバッファーとして機能する別のクエリを作成しましょう。 ファイル内の結果のテーブルの任意のセルを選択する 受信機、タブで選択 且つ Command テーブル/範囲から (データ — テーブル/範囲から) or 葉付き （シートより）:
Power Query に読み込まれたテーブルでは何もしません。たとえば、クエリを呼び出します。 古いデータ を押し ホーム — 閉じてロード — 閉じてロード… — 接続のみを作成 (ホーム — Close&Load — Close&Load to… — 接続のみ作成).
ステップ 3. 古いデータと新しいデータの結合
元のクエリに戻ります 申し込み コマンドを使用して、前のバッファ要求からの古いデータの下から追加します ホーム — リクエストの追加 (ホーム — 追加クエリ):
それで全部です！
Excelに戻る必要があります ホーム — 閉じてダウンロード (ホーム — クローズ & ロード) ボタンで構造全体を更新するために数回試してください すべて更新 タブ 且つ (データ — すべて更新). 更新のたびに、新しいデータは古いデータを置き換えませんが、更新履歴全体を保持して下にプッシュします。
必要に応じて、外部ソース (インターネット サイト、データベース、外部ファイルなど) からインポートするときに、履歴の古い値を保持するために同様のトリックを使用できます。
- 複数のデータ範囲にわたるピボット テーブル
- Power Query を使用してさまざまなファイルからテーブルを組み立てる
- 本のすべてのシートから XNUMX つのテーブルにデータを収集する