การแก้ไขอัตราสกุลเงินที่ขาดหายไปด้วย Power Query ใน LuckyTemplates

ในบทช่วยสอนนี้ คุณจะได้เรียนรู้วิธีใช้ Power Query สำหรับการแปลงสกุลเงินใน LuckyTemplates คุณสามารถดูวิดีโอทั้งหมดของบทช่วยสอนนี้ได้ที่ด้านล่างของบล็อกนี้

วิธีที่กล่าวถึงในบล็อกนี้จะช่วยคุณแก้ปัญหาในตารางอัตราสกุลเงินของคุณโดยใช้อัตราที่มีอยู่ล่าสุด

คุณจะเข้าใจวิธีการทำงานเมื่อตารางอัตราสกุลเงินไม่มีอัตราสกุลเงินในแต่ละวัน

สารบัญ

• การระบุข้อมูลที่ขาดหายไป
• การแยกสกุลเงินต่างๆ
• กรอกอัตราที่ขาดหายไป
• การรวมสกุลเงินทั้งหมด
• การสร้างความสัมพันธ์ในตัวแบบข้อมูล
• การเปรียบเทียบวิธี DAX และ Power Query
• บทสรุป

การระบุข้อมูลที่ขาดหายไป

ปัญหานี้อาจเกิดขึ้นได้หากตารางวันที่ไม่มีข้อมูลสำหรับวันหยุดหรือวันหยุดสุดสัปดาห์

หากคุณไม่มีอัตราในวันใดและไม่ได้คำนึงถึงมัน ระบบจะไม่แปลงเมตริกต่างๆ เช่น ยอดขายรวม

หากคุณดูที่ตารางอัตราสกุลเงินนี้ จะมีอัตราในสกุลเงินยูโรสำหรับเดือนมกราคม 2559

แต่สังเกตว่าวันที่ 27 และ 28 หายไปและไม่มีเรท ถ้าคุณไปที่เมทริกซ์ คุณจะเห็นว่าไม่มีอัตราสำหรับ 27 และ 28

อัตราสกุลเงินที่ไม่มีหน่วยวัดเป็นเพียงผลรวมของตารางอัตราสกุลเงินจริง

ผลรวม 26.6983 ในตารางนั้นไม่มีความหมาย เป็นเพียงผลรวมของอัตราสกุลเงินทั้งหมดที่อยู่เหนืออัตราดังกล่าว ซึ่งคุณไม่จำเป็นต้องบวกเพิ่ม เนื่องจากเป็นอัตราที่อยู่ภายใต้วันปัจจุบัน

ขณะนี้ สามารถแก้ไขได้ในPower Queryเนื่องจากนี่เป็นปัญหาการสร้างแบบจำลองข้อมูลที่เกี่ยวข้องกับการสอบถามและการรวม สิ่งนี้สามารถทำได้ด้วย DAX แต่จะง่ายและรวดเร็วขึ้นด้วย Power Query ใน LuckyTemplates

คลิกแปลงข้อมูลแล้วเปิดหน้าต่าง Power Query

นี่คือตารางที่มีข้อมูลขาดหายไป คุณยังสามารถดูในตารางอัตราสกุลเงินนี้ว่าประกอบด้วยยูโร ปอนด์ และดอลลาร์

และนี่คือตารางสุดท้ายที่มีค่าในวันที่ 27 และ 28 มกราคม นี่คือตารางหรือเอาต์พุตที่คุณควรมี

นำเข้าข้อมูลของคุณจากแหล่งใดก็ตามที่คุณได้รับมา และเพียงแค่เปลี่ยนประเภทข้อมูล

การแยกสกุลเงินต่างๆ

ถัดไป หากคุณมีมากกว่าหนึ่งสกุลเงิน สิ่งสำคัญคือต้องแยกและจัดกลุ่มสกุลเงินตามสัญลักษณ์

เมื่อคุณจัดกลุ่ม ให้ตรวจสอบให้แน่ใจว่าได้จัดกลุ่มไว้ในแถวทั้งหมด ตัวอย่างเช่น หากคุณคลิกยูโร ระบบจะแสดงเฉพาะอัตราสกุลเงินเป็นยูโรเท่านั้น

คุณจะเห็นว่ามีวันที่หายไปที่นี่เช่นเดียวกับสกุลเงินปอนด์

มีสิ่งนี้เรียกว่าInvoke Custom Functionใน LuckyTemplates นี่คือฟังก์ชันที่มี FillMissingRates

หากคุณคลิกยูโร คุณจะเห็นตารางที่สมบูรณ์ซึ่งมีข้อมูลที่ขาดหายไปซึ่งคุณเห็นในตารางที่ขยาย โดยจะเรียงจากอัตราที่เก่าที่สุดไปจนถึงอัตราที่ใหม่ที่สุด

ค้นหาว่าฟังก์ชันแบบกำหนดเองกำลังทำอะไรอยู่ หากคุณคลิกไอคอนรูปเฟืองใกล้กับ Invoke Custom Function คุณจะเห็นชื่อคอลัมน์และแหล่งที่มาที่ดึงมาจากคอลัมน์ All

ในการทำเช่นนั้น ให้เริ่มต้นด้วยตารางพื้นฐาน ทำการแปลง รับผลลัพธ์ที่คุณต้องการ แล้วใช้สิ่งนั้นเพื่อสร้างฟังก์ชัน มันสมเหตุสมผลกว่าการพยายามทำในตารางที่คุณมีตารางย่อยอื่นๆ ทั้งหมด

ตัวอย่างเช่น หากคุณต้องการเน้นที่สกุลเงินยูโร ให้กรองลงไปที่สัญลักษณ์

หากต้องการทราบวันที่ขาดหายไป ให้รวมเข้ากับตารางวันที่ของคุณ

นี่คือตารางสกุลเงินพื้นฐานที่กรองแล้วและตาราง DimDate:

สำหรับ Join Kind คุณสามารถใช้ Full Outer เพื่อดึงแถวทั้งหมดจากทั้งสองตาราง สิ่งนี้แสดงให้คุณเห็นว่ามีอะไรขาดหายไปและอัตราที่ตรงกัน

เมื่อคุณรวมเข้าด้วยกัน มันจะทำให้คุณมีตารางเต็มเป็นตารางย่อย

หลังจากนั้น เรียงลำดับแถวแล้วขยาย DimDate เพื่อรวมเฉพาะคอลัมน์วันที่

ตอนนี้คุณจะเห็นว่าทุกอย่างตรงกัน และเมื่อคุณจัดเรียงแถวตามเก่าสุดไปใหม่สุด คุณจะเห็นวันที่หายไปสองวันด้วยค่า Null

กรอกอัตราที่ขาดหายไป

สิ่งสำคัญคือต้องเรียงลำดับตามนี้ เพราะคุณจะต้องเติมค่าว่างทั้งหมดในทุกคอลัมน์ยกเว้น Date.1

หลังจากที่คุณกรอกข้อมูลแล้ว คุณจะเห็นค่าว่างเปลี่ยนเป็นยูโรและอัตราล่าสุดที่มี

ถัดไป ลบคอลัมน์ที่ไม่จำเป็น เช่น คอลัมน์วันที่ออกจากตาราง

คอลัมน์ Date.1 มีวันที่อยู่แล้ว จึงไม่จำเป็นต้องใช้คอลัมน์ Date จากนั้นตั้งค่า Crossrate เป็นเลขทศนิยม

สุดท้าย จัดลำดับใหม่และเปลี่ยนชื่อคอลัมน์ และตรวจสอบให้แน่ใจว่าได้ตั้งค่าประเภทข้อมูลของคุณแล้ว

ตอนนี้คุณมีตารางอัตราสกุลเงินที่สมบูรณ์แล้ว

อย่างไรก็ตาม ใช้ได้กับตารางนี้เท่านั้น คุณต้องหาวิธีใช้ตรรกะเดียวกันกับตารางที่จัดกลุ่มสกุลเงิน

การรวมสกุลเงินทั้งหมด

หากคุณมีสกุลเงินที่แตกต่างกัน การรักษาตารางอัตราที่แตกต่างกันนั้นทำได้ยาก ดังนั้น คุณต้องรวมเข้าด้วยกันเพื่อสร้างตารางสกุลเงินเดียว

ในการเริ่มต้น ให้นำตารางที่ มีอยู่ของคุณมาสร้างฟังก์ชันโดยคลิกตารางแล้วเลือกสร้างฟังก์ชัน จากนั้นป้อนชื่อฟังก์ชัน

หลังจากนั้น ไปที่โปรแกรมแก้ไขขั้นสูง ลบการคำนวณที่คุณไม่ต้องการ ในกรณีนี้ ให้กำจัดแหล่งที่มา กรอง และเปลี่ยนไวยากรณ์

ถัดไป ตั้งชื่อพารามิเตอร์ของคุณว่า Source แล้วป้อน Let เนื่องจากแถวที่กรองถูกเอาออก ให้เปลี่ยนค่าภายในไวยากรณ์ของ Merged Query เป็น Source หลังจากนั้น ให้ลบ Source ที่ส่วนท้ายของไวยากรณ์ จากนั้นคลิก Done

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

สิ่งต่อไปที่ต้องทำคือลบคอลัมน์ทั้งหมดยกเว้นข้อมูลทั้งหมด จากนั้นขยายคอลัมน์นั้นและยกเลิกการเลือกการตั้งค่า "ใช้ชื่อคอลัมน์เดิมเป็นคำนำหน้า" หลังจากนั้นคลิก ตกลง และเปลี่ยนประเภทคอลัมน์

ตั้งค่าประเภทคอลัมน์ Date, Ticker และ Crossrate เป็น Date, Text และ Decimal ตามลำดับ เมื่อเสร็จแล้ว ให้คลิก ปิด & นำไปใช้

ขณะนี้ คุณจะได้รับตารางอัตราสกุลเงินแบบรวมโดยไม่มีวันที่และอัตราที่ขาดหายไป

การสร้างความสัมพันธ์ในตัวแบบข้อมูล

ในรูปแบบข้อมูลของคุณ คุณสามารถดูตารางFactCurrencyRates ตอนนี้ สร้างความสัมพันธ์ Ticker to Ticker และ Date to Date กับตาราง���ั้นและตาราง DimCurrencyRates และ DimDate

ในเมทริกซ์ของคุณ ให้ดึงCurrency Rate No Missingเพื่อดูว่าตอนนี้มีอัตราสำหรับวันที่ 27 และ 28 ทั้งหมดเสร็จสิ้นใน Power Query

คอลัมน์อัตราสกุลเงินที่รายงานล่าสุดยังแสดงอัตราของวันที่ขาดหายไปอีกด้วย มันแสดงอัตราและค่าเดียวกัน แต่ข้อแตกต่างระหว่างคอลัมน์เหล่านี้คือคอลัมน์นี้สร้างขึ้นใน DAX

นี่คือการวัดและไวยากรณ์ของคอลัมน์นั้น มันไม่ง่ายเหมือนการใช้ ฟังก์ชัน ใน Power Query

เมื่อดูที่ตาราง คุณจะเห็นคอลัมน์ยอดขายที่แปลงแล้วไม่ขาดหายไป แสดงยอดขายที่แปลงโดยใช้คอลัมน์อัตราสกุลเงินที่ขาดหายไป คอลัมน์อื่นๆ ที่มีการแปลงยอดขายจะใช้คอลัมน์ที่สร้างใน DAX

ฟังก์ชันที่ใช้ในการแปลงยอดขายคือหากคุณดูหน่วยวัดสำหรับคอลัมน์นั้น คุณจะเห็น SUMX อยู่เหนือตาราง DimDate

การเปรียบเทียบวิธี DAX และ Power Query

คุณสามารถเปรียบเทียบทั้งคอลัมน์การขายที่แปลงแล้วซึ่งใช้ DAX และ Power Query ใน LuckyTemplates

ในการทำเช่นนั้น ให้กำจัดคอลัมน์ทั้งหมดยกเว้นคอลัมน์การขายที่แปลงแล้ว จากนั้น เปิดเครื่องวิเคราะห์ประสิทธิภาพ แล้วคลิก เริ่มการบันทึก

จากนั้นเปิด DAX Studio เป็นแอปพลิเคชันที่แสดงให้คุณเห็นว่าเหตุใดสิ่งต่างๆ จึงทำงานอย่างไรในโมเดลของคุณ หลังจากนั้น ให้คัดลอกแบบสอบถามของเมทริกซ์ของคุณแล้ววางลงในสตูดิโอ

สำหรับการเปรียบเทียบ ให้เปลี่ยนหนึ่งรายการเป็นความคิดเห็นก่อน เพื่อไม่ให้รบกวนคอลัมน์อื่นๆ ที่กำลังทดสอบ

จากนั้นโหลด Server Timings and Query Plan เนื่องจากคุณกำลังเปรียบเทียบประสิทธิภาพ ให้ล้างแคชก่อนที่จะเรียกใช้การเปรียบเทียบ

หลังจากรันการทดสอบ คุณจะเห็นว่าคอลัมน์ที่ใช้ DAX มีการสแกนจำนวนมากและมีเวลาทั้งหมด 71 มิลลิวินาที

ตอนนี้ เรียกใช้คอลัมน์อื่นที่ใช้ Power query แต่ก่อนอื่น ให้เปลี่ยนคอลัมน์อื่นในไวยากรณ์เป็นความคิดเห็น

คุณจะเห็นว่าคอลัมน์ทำงานเพียง 25 มิลลิวินาทีและมีการสแกนเพียง 7 ครั้ง

คุณสามารถเห็นได้อย่างชัดเจนว่าวิธีใดทำงานเร็วกว่าและดีกว่าระหว่างวิธี DAX และ Power Query

บทสรุป

ข้อดีของการใช้วิธี Power Query ใน LuckyTemplates คือข้อมูลถูกเก็บไว้แล้ว ดังนั้นการวัดสามารถหาอัตราแล้วดึงออกมาโดยใช้ฟังก์ชันง่ายๆ

อีกอย่างเกี่ยวกับวิธีนี้ก็คือ มันจะไม่ช้าลงหากการคำนวณมีความซับซ้อน

อย่างไรก็ตาม วิธีนี้ใช้ได้เฉพาะในกรณีที่ข้อมูลที่คุณขอไม่จำเป็นต้องแปลงในทันที ถ้าเป็นเช่นนั้น คุณต้องใช้ DAX

สิ่งที่ดีที่สุด