در حال ذخیره تاریخچه به روز رسانی پرس و جوی Power Query

تقریباً در هر دوره آموزشی Power Query، وقتی به نحوه به روز رسانی کوئری های ایجاد شده می رسیم و مردم می بینند که چگونه داده های جدید جایگزین داده های قدیمی در هنگام به روز رسانی می شوند، یکی از شنوندگان از من می پرسد: "آیا می توان مطمئن شد که هنگام به روز رسانی، داده های قدیمی هستند. همچنین در جایی ذخیره شد و کل تاریخچه به روز رسانی قابل مشاهده بود؟

این ایده جدید نیست و پاسخ استاندارد به آن "نه" خواهد بود - Power Query به طور پیش فرض برای جایگزینی داده های قدیمی با داده های جدید پیکربندی شده است (که در اکثر موارد مورد نیاز است). با این حال، اگر واقعاً می خواهید، می توانید از این محدودیت عبور کنید. و روش، همانطور که در ادامه خواهید دید، بسیار ساده است.

مثال زیر را در نظر بگیرید.

بیایید فرض کنیم که یک فایل از مشتری به عنوان داده ورودی داریم (بیایید آن را صدا کنیم، فرض کنید، منبع) با لیستی از محصولاتی که می خواهد در قالب یک جدول پویا “هوشمند” به نام بخرد کاربرد:

در یک فایل دیگر (بیایید آن را با قیاس صدا کنیم گیرنده) یک کوئری ساده برای وارد کردن یک جدول با محصولات از منبع از طریق ایجاد می کنیم داده – دریافت داده – از فایل – از کتاب کار اکسل (داده - دریافت داده - از فایل - از کتاب کار اکسل) و جدول به دست آمده را در شیت آپلود کنید:

اگر در آینده مشتری تصمیم به ایجاد تغییراتی در سفارش در پرونده خود داشته باشد منبع، سپس پس از به روز رسانی درخواست ما (با کلیک راست یا از طریق داده - تازه کردن همه) داده های جدید را در فایل مشاهده خواهیم کرد گیرنده - همه استاندارد

حال بیایید مطمئن شویم که هنگام به‌روزرسانی، داده‌های قدیمی با داده‌های جدید جایگزین نمی‌شوند، بلکه داده‌های جدید به داده‌های قدیمی اضافه می‌شوند - و با اضافه کردن تاریخ-زمان، به طوری که می‌توان دید این تغییرات خاص چه زمانی بوده است. ساخته شده است.

مرحله 1. افزودن تاریخ-زمان به درخواست اصلی

بیایید یک درخواست باز کنیم کاربردوارد کردن داده های ما از منبعو ستونی با تاریخ و زمان به روز رسانی به آن اضافه کنید. برای این کار می توانید از دکمه استفاده کنید ستون سفارشی برگ اضافه کردن یک ستون (افزودن ستون - ستون سفارشی)و سپس تابع را وارد کنید DateTime.LocalNow - آنالوگ تابع TDATA (اکنون) در مایکروسافت اکسل:

پس از کلیک روی OK شما باید با یک ستون زیبا مانند این روبرو شوید (فراموش نکنید که قالب تاریخ و زمان آن را با نماد موجود در سر ستون تنظیم کنید):

اگر می خواهید، برای صفحه بارگذاری شده در برگه این ستون، می توانید قالب تاریخ-زمان را با ثانیه برای دقت بیشتر تنظیم کنید (باید دو نقطه و "ss" را به قالب استاندارد اضافه کنید):

مرحله 2: پرس و جو برای داده های قدیمی

حالا بیایید پرس و جو دیگری ایجاد کنیم که به عنوان بافری عمل می کند که داده های قدیمی را قبل از به روز رسانی ذخیره می کند. انتخاب هر سلول از جدول به دست آمده در فایل گیرندهرا در برگه انتخاب کنید داده ها فرمان از جدول/محدوده (داده ها - از جدول / محدوده) or با برگ (از برگه):

ما با جدول بارگذاری شده در Power Query کاری انجام نمی دهیم، برای مثال پرس و جو را فراخوانی می کنیم. داده های قدیمی و مطبوعات صفحه اصلی — بستن و بارگیری — بستن و بارگیری در… — فقط اتصال ایجاد کنید (صفحه اصلی — بستن و بارگذاری — بستن و بارگیری به… — فقط اتصال ایجاد کنید).

مرحله 3. پیوستن به داده های قدیمی و جدید

اکنون به پرسش اصلی خود بازگردیم کاربرد و از زیر داده های قدیمی درخواست بافر قبلی را با دستور به آن اضافه کنید صفحه اصلی — افزودن درخواست ها (صفحه اصلی - ضمیمه پرسش ها):

این همه!

باقی مانده است که از طریق اکسل برگردیم صفحه اصلی — بستن و دانلود کنید (صفحه اصلی - بستن و بارگیری) و چند بار سعی کنید کل ساختار خود را با دکمه به روز کنید همه را بروز رسانی کن برگ داده ها (داده ها - بازخوانی همه). با هر به‌روزرسانی، داده‌های جدید جایگزین داده‌های قدیمی نمی‌شوند، بلکه آن‌ها را به زیر فشار می‌دهند و کل تاریخچه به‌روزرسانی را حفظ می‌کنند:

ترفند مشابهی را می توان هنگام وارد کردن از هر منبع خارجی (سایت های اینترنتی، پایگاه های داده، فایل های خارجی و غیره) استفاده کرد تا در صورت نیاز مقادیر قدیمی را برای تاریخچه نگه دارید.

• جدول محوری در چندین محدوده داده
• جمع آوری جداول از فایل های مختلف با استفاده از Power Query
• جمع آوری داده ها از تمام برگه های کتاب در یک جدول