การบันทึกประวัติการอัปเดตคิวรี Power Query

ในการฝึกอบรม Power Query เกือบทุกรายการ เมื่อเราทราบวิธีอัปเดตการสืบค้นที่สร้างและผู้คนเห็นว่าข้อมูลใหม่แทนที่ข้อมูลเก่าเมื่ออัปเดตอย่างไร ผู้ฟังคนหนึ่งถามฉันว่า: "เป็นไปได้ไหมที่จะตรวจสอบให้แน่ใจว่าเมื่ออัปเดต ข้อมูลเก่าคือ ถูกบันทึกไว้ที่ไหนสักแห่งและประวัติการอัปเดตทั้งหมดก็ปรากฏให้เห็น?

แนวคิดนี้ไม่ใช่เรื่องใหม่และคำตอบมาตรฐานคือ "ไม่" – Power Query ได้รับการกำหนดค่าตามค่าเริ่มต้นเพื่อแทนที่ข้อมูลเก่าด้วยข้อมูลใหม่ (ซึ่งจำเป็นในกรณีส่วนใหญ่) อย่างไรก็ตาม หากคุณต้องการจริงๆ คุณสามารถหลีกเลี่ยงข้อจำกัดนี้ได้ และวิธีการอย่างที่คุณเห็นในภายหลังนั้นง่ายมาก

ลองพิจารณาตัวอย่างต่อไปนี้

สมมติว่าเรามีไฟล์จากลูกค้าเป็นข้อมูลเข้า (เรียกมันว่า แหล่ง) พร้อมรายการสินค้าที่เขาต้องการซื้อในรูปแบบไดนามิก “สมาร์ท” ตารางชื่อ การใช้งาน:

การบันทึกประวัติการอัปเดตคิวรี Power Query

ในไฟล์อื่น (เรียกโดยการเปรียบเทียบ ผู้รับ) เราสร้างแบบสอบถามอย่างง่ายเพื่อนำเข้าตารางที่มีผลิตภัณฑ์จากแหล่งที่มาผ่าน ข้อมูล – รับข้อมูล – จากไฟล์ – จากสมุดงาน Excel (ข้อมูล — รับข้อมูล — จากไฟล์ — จากสมุดงาน Excel) และอัปโหลดตารางผลลัพธ์ไปยังแผ่นงาน:

การบันทึกประวัติการอัปเดตคิวรี Power Query

หากในอนาคตลูกค้าตัดสินใจเปลี่ยนแปลงคำสั่งซื้อในไฟล์ของเขา แหล่งจากนั้นหลังจากอัปเดตคำขอของเรา (โดยการคลิกขวาหรือผ่านทาง ข้อมูล – รีเฟรชทั้งหมด) เราจะเห็นข้อมูลใหม่ในไฟล์ ผู้รับ - มาตรฐานทั้งหมด

ตอนนี้ ให้ตรวจสอบให้แน่ใจว่าเมื่ออัปเดต ข้อมูลเก่าจะไม่ถูกแทนที่ด้วยข้อมูลใหม่ แต่ข้อมูลใหม่จะถูกผนวกเข้ากับข้อมูลเก่า - และด้วยการเพิ่มวันที่-เวลา เพื่อให้สามารถมองเห็นได้เมื่อการเปลี่ยนแปลงเฉพาะเหล่านี้ ทำ.

ขั้นตอนที่ 1. การเพิ่มวันที่-เวลาให้กับข้อความค้นหาเดิม

มาเปิดคำร้องกันเถอะ การใช้งานการนำเข้าข้อมูลของเราจาก แหล่งและเพิ่มคอลัมน์ที่มีวันที่-เวลาของการอัปเดตลงไป ในการดำเนินการนี้ คุณสามารถใช้ปุ่ม คอลัมน์ที่กำหนดเอง แถบ การเพิ่มคอลัมน์ (เพิ่มคอลัมน์ — คอลัมน์กำหนดเอง)แล้วเข้าสู่ฟังก์ชัน DateTime.LocalNow – ฟังก์ชั่นอะนาล็อก ทีดาต้า (ตอนนี้) ใน Microsoft Excel:

การบันทึกประวัติการอัปเดตคิวรี Power Query

หลังจากคลิกที่ OK คุณควรลงเอยด้วยคอลัมน์ที่สวยงามเช่นนี้ (อย่าลืมกำหนดรูปแบบวันที่-เวลาด้วยไอคอนในส่วนหัวของคอลัมน์):

การบันทึกประวัติการอัปเดตคิวรี Power Query

หากต้องการ สำหรับเพลตที่อัปโหลดไปยังชีตสำหรับคอลัมน์นี้ คุณสามารถตั้งค่ารูปแบบวันที่-เวลาเป็นวินาทีเพื่อความแม่นยำที่มากขึ้น (คุณจะต้องเพิ่มเครื่องหมายทวิภาคและ "ss" ในรูปแบบมาตรฐาน):

การบันทึกประวัติการอัปเดตคิวรี Power Query

ขั้นตอนที่ 2: สืบค้นข้อมูลเก่า

ตอนนี้ มาสร้างแบบสอบถามอื่นที่จะทำหน้าที่เป็นบัฟเฟอร์ที่บันทึกข้อมูลเก่าก่อนที่จะอัปเดต การเลือกเซลล์ใด ๆ ของตารางผลลัพธ์ในไฟล์ ผู้รับ, เลือกบนแท็บ ข้อมูล คำสั่ง จากตาราง/ช่วง (ข้อมูล — จากตาราง/ช่วง) or มีใบ (จากแผ่นงาน):

การบันทึกประวัติการอัปเดตคิวรี Power Query

เราไม่ทำอะไรกับตารางที่โหลดใน Power Query เราเรียกว่าคิวรี ตัวอย่างเช่น ข้อมูลเก่า และกด หน้าแรก — ปิดและโหลด — ปิดและโหลดไปที่… — สร้างการเชื่อมต่อเท่านั้น (หน้าแรก — ปิด&โหลด — ปิด&โหลดไปที่… — สร้างการเชื่อมต่อเท่านั้น).

ขั้นตอนที่ 3 เข้าร่วมข้อมูลเก่าและใหม่

กลับไปที่คำถามเดิมของเรา การใช้งาน และเพิ่มจากด้านล่างข้อมูลเก่าจากคำขอบัฟเฟอร์ก่อนหน้าด้วยคำสั่ง หน้าแรก — เพิ่มคำขอ (หน้าแรก — ต่อท้ายแบบสอบถาม):

การบันทึกประวัติการอัปเดตคิวรี Power Query

ทั้งหมดนี้!

มันยังคงกลับไปสู่ ​​Excel ผ่าน หน้าแรก — ปิดและดาวน์โหลด (หน้าแรก — ปิด&โหลด) และลองสองสามครั้งเพื่ออัปเดตโครงสร้างทั้งหมดของเราด้วยปุ่ม การปรับปรุงทั้งหมด แถบ ข้อมูล (ข้อมูล — รีเฟรชทั้งหมด). ในการอัปเดตแต่ละครั้ง ข้อมูลใหม่จะไม่แทนที่ข้อมูลเก่า แต่จะผลักดันให้อยู่ด้านล่าง โดยจะเก็บประวัติการอัปเดตทั้งหมดไว้:

การบันทึกประวัติการอัปเดตคิวรี Power Query

คุณสามารถใช้เคล็ดลับที่คล้ายกันนี้เมื่อนำเข้าจากแหล่งภายนอก (ไซต์อินเทอร์เน็ต ฐานข้อมูล ไฟล์ภายนอก ฯลฯ) เพื่อเก็บค่าเก่าไว้สำหรับประวัติหากคุณต้องการ

  • ตาราง Pivot ในหลายช่วงข้อมูล
  • การประกอบตารางจากไฟล์ต่างๆ โดยใช้ Power Query
  • รวบรวมข้อมูลจากหนังสือทุกแผ่นในตารางเดียว

เขียนความเห็น