สร้างสูตร Power Query ใน Excel

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

1. Ribbon ตัวแก้ไข Power Query ที่คุณใช้จัดรูปแบบข้อมูลของคุณ

2. บานหน้าต่างคิวรีที่คุณใช้เพื่อค้นหาแหล่งข้อมูลและตาราง

3. เมนูบริบทที่เป็นทางลัดไปยังคําสั่งใน Ribbon ที่สะดวก

4. การแสดงตัวอย่างข้อมูลที่แสดงผลลัพธ์ของขั้นตอนที่นําไปใช้กับข้อมูล

5. บานหน้าต่าง การตั้งค่าคิวรี ที่แสดงคุณสมบัติและขั้นตอนแต่ละขั้นตอนในคิวรี

ในเบื้องหลัง แต่ละขั้นตอนในคิวรีจะยึดตามสูตรที่มองเห็นได้ในแถบสูตร

อาจมีบางครั้งที่คุณต้องการปรับเปลี่ยนหรือสร้างสูตร สูตรใช้ Power Query ภาษาสูตร ซึ่งคุณสามารถใช้เพื่อสร้างทั้งนิพจน์อย่างง่ายและซับซ้อนได้ สําหรับข้อมูลเพิ่มเติมเกี่ยวกับไวยากรณ์ อาร์กิวเมนต์ ข้อสังเกต ฟังก์ชัน และตัวอย่าง ให้ดูที่ Power Query ภาษาสูตร M

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

กระบวนงาน

1. เมื่อต้องการนําเข้าข้อมูล ให้เลือก ข้อมูล > จากเว็บ ใส่ "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" ในกล่อง URL แล้วเลือก ตกลง

2. ในกล่องโต้ตอบ ตัวนําทาง ให้เลือกตาราง ผลลัพธ์ [แก้ไข] ทางด้านซ้าย แล้วเลือก แปลงข้อมูล ที่ด้านล่าง ตัวแก้ไข Power Query จะปรากฏขึ้น

3. เมื่อต้องการเปลี่ยนชื่อคิวรีเริ่มต้น ในบานหน้าต่าง การตั้งค่าคิวรี ภายใต้ คุณสมบัติ ให้ลบ "ผลลัพธ์ [แก้ไข]" แล้วใส่ "แชมป์ UEFA"

4. เมื่อต้องการเอาคอลัมน์ที่ไม่ต้องการออก ให้เลือกคอลัมน์แรก คอลัมน์ที่สี่ และคอลัมน์ที่ห้า แล้วเลือก หน้าแรก > เอาคอลัมน์ออก > เอาคอลัมน์อื่นออก

5. เมื่อต้องการเอาค่าที่ไม่ต้องการออก ให้เลือก คอลัมน์ 1 เลือก หน้าแรก > แทนที่ค่า ใส่ "รายละเอียด" ในกล่อง ค่าที่จะค้นหา แล้วเลือก ตกลง

6. เมื่อต้องการเอาแถวที่มีคําว่า "ปี' ในแถวเหล่านั้นออก ให้เลือกลูกศรตัวกรองใน คอลัมน์ 1 ล้างกล่องกาเครื่องหมายที่อยู่ถัดจาก "ปี" แล้วเลือก ตกลง

7. เมื่อต้องการเปลี่ยนชื่อส่วนหัวของคอลัมน์ ให้ดับเบิลคลิกแต่ละส่วนหัว แล้วเปลี่ยน "คอลัมน์ 1" เป็น "ปี", "คอลัมน์ 4" เป็น "ผู้ชนะ" และ "คอลัมน์ 5" เป็น "คะแนนสุดท้าย"

8. เมื่อต้องการบันทึกคิวรี ให้เลือก หน้าแรก > ปิด & โหลด

ผลลัพธ์

ตารางต่อไปนี้เป็นข้อมูลสรุปของแต่ละขั้นตอนที่นําไปใช้และสูตรที่สอดคล้องกัน

ขั้นตอนคิวรีและงาน	สูตร
แหล่งที่มา เชื่อมต่อกับแหล่งข้อมูลบนเว็บ	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
การนำทาง เลือกตารางเพื่อเชื่อมต่อ	=Source{2}[Data]
ชนิดที่เปลี่ยนแปลง เปลี่ยนชนิดข้อมูล (ซึ่ง Power Query ทําโดยอัตโนมัติ)	= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})
เอาคอลัมน์อื่นออกแล้ว เอาคอลัมน์อื่นออกเพื่อแสดงเฉพาะคอลัมน์ที่สนใจเท่านั้น	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
ค่าที่ถูกแทนที่ แทนที่ค่าเพื่อล้างค่าในคอลัมน์ที่เลือก	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
แถวที่กรอง กรองค่าในคอลัมน์	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
คอลัมน์ที่เปลี่ยนชื่อ ส่วนหัวของคอลัมน์ที่เปลี่ยนแปลงเพื่อให้สื่อความหมาย	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

สําคัญ    โปรดใช้ความระมัดระวังในการแก้ไขขั้นตอน 'แหล่งข้อมูล' 'การนําทาง'  และ 'ชนิดที่เปลี่ยนแปลง' เนื่องจากขั้นตอนเหล่านั้นถูกสร้างขึ้นโดย Power Query เพื่อกําหนดและตั้งค่าแหล่งข้อมูล

แสดงหรือซ่อนแถบสูตร

แถบสูตรจะแสดงตามค่าเริ่มต้น แต่ถ้ามองไม่เห็น คุณสามารถแสดงแถบสูตรอีกครั้งได้

• เลือก มุมมอง > เค้าโครง > แถบสูตร

Edit a formula in the formula bar

1. เมื่อต้องการเปิดคิวรี ให้ค้นหาคิวรีที่โหลดไว้ก่อนหน้านี้จากตัวแก้ไข Power Query เลือกเซลล์ในข้อมูล แล้วเลือก คิวรี > แก้ไข สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel

2. ในบานหน้าต่าง การตั้งค่าคิวรี ภายใต้ ขั้นตอนที่นําไปใช้ ให้เลือกขั้นตอนที่คุณต้องการแก้ไข

3. ในแถบสูตร ให้ค้นหาและเปลี่ยนค่าพารามิเตอร์ แล้วเลือกไอคอน Enter หรือกด Enter ตัวอย่างเช่น เปลี่ยนสูตรนี้เพื่อเก็บคอลัมน์ 2:
   
   ก่อน: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   หลังจาก:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. เลือกไอคอน ใส่ หรือกด Enter เพื่อดูผลลัพธ์ใหม่ที่แสดงในการแสดงตัวอย่างข้อมูล

5. เมื่อต้องการดูผลลัพธ์ในเวิร์กชีต Excel ให้เลือก หน้าแรก > ปิด & โหลด

สร้างสูตรในแถบสูตร

สําหรับตัวอย่างสูตรอย่างง่าย ให้แปลงค่าข้อความเป็นตัวพิมพ์ที่เหมาะสมโดยใช้ฟังก์ชัน Text.Proper

1. เมื่อต้องการเปิดคิวรีเปล่า ใน Excel ให้เลือก ข้อมูล > รับ > ข้อมูลจากแหล่งข้อมูลอื่น > คิวรีเปล่า สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel

2. ในแถบสูตร ให้ใส่=Text.Proper("text value")แล้วเลือกไอคอน Enter หรือกด Enter
   
   ผลลัพธ์จะแสดงใน แสดงตัวอย่างข้อมูล

3. เมื่อต้องการดูผลลัพธ์ในเวิร์กชีต Excel ให้เลือก หน้าแรก > ปิด & โหลด

ผลลัพธ์:

 เมื่อคุณสร้างสูตร Power Query จะตรวจสอบไวยากรณ์ของสูตร อย่างไรก็ตาม เมื่อคุณแทรก จัดลําดับใหม่ หรือลบขั้นตอนระดับกลางในคิวรี คุณอาจตัดคิวรี  ตรวจสอบผลลัพธ์ในการแสดงตัวอย่างข้อมูลเสมอ

สําคัญ    โปรดใช้ความระมัดระวังในการแก้ไขขั้นตอน 'แหล่งข้อมูล' 'การนําทาง'  และ 'ชนิดที่เปลี่ยนแปลง' เนื่องจากขั้นตอนเหล่านั้นถูกสร้างขึ้นโดย Power Query เพื่อกําหนดและตั้งค่าแหล่งข้อมูล

แก้ไขสูตรโดยใช้กล่องโต้ตอบ

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

1. เมื่อต้องการเปิดคิวรี ให้ค้นหาคิวรีที่โหลดไว้ก่อนหน้านี้จากตัวแก้ไข Power Query เลือกเซลล์ในข้อมูล แล้วเลือก คิวรี > แก้ไข สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel

2. ในบานหน้าต่าง การตั้งค่าคิวรี ภายใต้ ขั้นตอนที่นําไปใช้ ให้เลือกไอคอน แก้ไขการตั้งค่า ของขั้นตอนที่คุณต้องการแก้ไขหรือคลิกขวาที่ขั้นตอน แล้วเลือก แก้ไขการตั้งค่า

3. ในกล่องโต้ตอบ ให้ทําการเปลี่ยนแปลงของคุณ แล้วเลือก ตกลง

แทรกขั้นตอน

หลังจากที่คุณดําเนินการขั้นตอนคิวรีที่ปรับรูปร่างข้อมูลของคุณใหม่แล้ว ขั้นตอนคิวรีจะถูกเพิ่มไว้ด้านล่างของขั้นตอนคิวรีปัจจุบัน แต่เมื่อคุณแทรกขั้นตอนคิวรีในช่วงกลางของขั้นตอน ข้อผิดพลาดอาจเกิดขึ้นในขั้นตอนถัดไป Power Query แสดงคําเตือน แทรกขั้นตอน เมื่อคุณพยายามแทรกขั้นตอนใหม่และขั้นตอนใหม่จะเปลี่ยนเขตข้อมูล เช่น ชื่อคอลัมน์ ที่ใช้ในขั้นตอนใดๆ ที่ทําตามขั้นตอนที่แทรก

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

2. เลือกไอคอน เพิ่มขั้นตอน ทางด้านซ้ายของแถบสูตร อีกวิธีหนึ่งคือ คลิกขวาที่ขั้นตอน แล้วเลือก แทรกขั้นตอนหลังจาก สูตรใหม่จะถูกสร้างขึ้นในรูปแบบ :
   
   = <nameOfTheStepToReference>เช่น =Production.WorkOrder

3. พิมพ์สูตรใหม่โดยใช้รูปแบบ:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   ตัวอย่างเช่น สมมติว่าคุณมีตารางที่มีคอลัมน์เพศ และคุณต้องการเพิ่มคอลัมน์ที่มีค่า "Ms" ทั้งนี้ขึ้นอยู่กับเพศของบุคคล สูตรจะเป็น:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

จัดลําดับขั้นตอนใหม่

• ในบานหน้าต่าง การตั้งค่าคิวรี ภายใต้ ขั้นตอนที่นําไปใช้ ให้คลิกขวาที่ขั้นตอน แล้วเลือก ย้ายขึ้น หรือ ย้ายลง

ลบขั้นตอน

• เลือกไอคอน ลบ ทางด้านซ้ายของขั้นตอน หรือคลิกขวาที่ขั้นตอน แล้วเลือก ลบ หรือ ลบจนกว่าจะสิ้นสุด ไอคอน ลบ จะพร้อมใช้งานทางด้านซ้ายของแถบสูตร

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

ตัวอย่างเช่น คุณมีตาราง Excel ที่เรียกว่า ใบสั่งซื้อ ที่มีคอลัมน์ ProductName ที่คุณต้องการแปลงเป็นตัวพิมพ์ที่เหมาะสม 

ก่อน:

หลัง:

เมื่อคุณสร้างคิวรีขั้นสูง คุณจะสร้างชุดของขั้นตอนสูตรคิวรีที่ยึดตามนิพจน์ let  ใช้นิพจน์ let เพื่อกําหนดชื่อและคํานวณค่าที่อ้างอิงโดยส่วนคําสั่ง in ซึ่งกําหนด Step ตัวอย่างนี้จะส่งกลับผลลัพธ์เดียวกันกับผลลัพธ์ในส่วน "สร้างสูตรในแถบสูตร"

let  
    Source = Text.Proper("hello world")
in  
    Source  

คุณจะเห็นว่าแต่ละขั้นตอนสร้างในขั้นตอนก่อนหน้าโดยอ้างอิงขั้นตอนตามชื่อ เป็นตัวเตือน ภาษาสูตร Power Query เป็นแบบตรงตามตัวพิมพ์ใหญ่-เล็ก

ขั้นที่ 1: เปิดเครื่องมือแก้ไขขั้นสูง

1. ใน Excel ให้เลือก ข้อมูล > รับข้อมูล > แหล่งข้อมูลอื่น > คิวรีเปล่า สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel

2. ในตัวแก้ไข Power Query ให้เลือก > หน้าแรก เครื่องมือแก้ไขขั้นสูง ซึ่งจะเปิดขึ้นพร้อมเทมเพลตของนิพจน์ให้

ขั้นที่ 2: กําหนดแหล่งข้อมูล

1. สร้างนิพจน์ let โดยใช้ Excel.CurrentWorkbook ฟังก์ชันดังนี้:
   
   let#x1
   
   in
       
   
   #x4Source

2. เมื่อต้องการโหลดคิวรีลงในเวิร์กชีต ให้เลือก เสร็จสิ้น แล้วเลือก หน้าแรก > ปิด & โหลด > ปิดการโหลด & โหลด

ผลลัพธ์:

ขั้นที่ 3: เลื่อนระดับแถวแรกเป็นส่วนหัว

1. เมื่อต้องการเปิดคิวรี จากเวิร์กชีต ให้เลือกเซลล์ในข้อมูล แล้วเลือก คิวรี > แก้ไข สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel (Power Query)

2. ในตัวแก้ไข Power Query ให้เลือก เครื่องมือแก้ไขขั้นสูง > หน้าแรก ซึ่งจะเปิดพร้อมกับคําสั่งที่คุณสร้างในขั้นตอนที่ 2: กําหนดแหล่งข้อมูล

3. ในนิพจน์ let ให้เพิ่ม #"First Row as Header" และ Table ฟังก์ชัน PromoteHeaders ดังนี้:
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
       #"First Row as Header"#x3#"First Row as Header" = Table.PromoteHeaders(Source)
   

4. เมื่อต้องการโหลดคิวรีลงในเวิร์กชีต ให้เลือก เสร็จสิ้น แล้วเลือก หน้าแรก > ปิด & โหลด > ปิดการโหลด & โหลด

ผลลัพธ์:

ขั้นที่ 4: เปลี่ยนแต่ละค่าในคอลัมน์ให้เป็นตัวพิมพ์ที่เหมาะสม

1. เมื่อต้องการเปิดคิวรี จากเวิร์กชีต ให้เลือกเซลล์ในข้อมูล แล้วเลือก คิวรี > แก้ไข สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel

2. ในตัวแก้ไข Power Query ให้เลือก หน้าแรก > เครื่องมือแก้ไขขั้นสูง ซึ่งจะเปิดพร้อมกับคําสั่งที่คุณสร้างใน ขั้นที่ 3: เลื่อนระดับแถวแรกเป็นส่วนหัว

3. ในนิพจน์ let ให้แปลงค่าคอลัมน์ ProductName แต่ละค่าเป็นข้อความที่เหมาะสมโดยใช้ฟังก์ชัน Table.TransformColumns ซึ่งอ้างถึงขั้นตอนของสูตรคิวรี "First Row as Header" ก่อนหน้า การเพิ่ม #"Capitalized Each Word" ลงในแหล่งข้อมูล แล้วกําหนด #"Capitalized Each Word" ให้กับผลลัพธ์
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
       #"First Row as Header" = Table.PromoteHeaders(Source),
       #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
   in
       #"Capitalized Each Word"

4. เมื่อต้องการโหลดคิวรีลงในเวิร์กชีต ให้เลือก เสร็จสิ้น แล้วเลือก หน้าแรก > ปิด & โหลด > ปิดการโหลด & โหลด

ผลลัพธ์:

คุณสามารถควบคุมลักษณะการทํางานของแถบสูตรในตัวแก้ไข Power Query สําหรับเวิร์กบุ๊กทั้งหมดของคุณได้

แสดงหรือซ่อนแถบสูตร

1. เลือก ตัวเลือก> ไฟล์ และ การตั้งค่า > ตัวเลือกคิวรี

2. ในบานหน้าต่างด้านซ้าย ภายใต้ GLOBAL ให้เลือก ตัวแก้ไข Power Query

3. ในบานหน้าต่างด้านขวา ภายใต้ เค้าโครง ให้เลือกหรือล้าง แสดงแถบสูตร

เปิดหรือปิด M Intellisense

1. เลือก ตัวเลือก> ไฟล์ และ การตั้งค่า > ตัวเลือกคิวรี

2. ในบานหน้าต่างด้านซ้าย ภายใต้ GLOBAL ให้เลือก ตัวแก้ไข Power Query

3. ในบานหน้าต่างด้านขวา ภายใต้ สูตร ให้เลือกหรือล้าง เปิดใช้งาน M Intellisense ในแถบสูตร ตัวแก้ไขขั้นสูง และกล่องโต้ตอบคอลัมน์แบบกําหนดเอง

หมายเหตุ    การเปลี่ยนแปลงการตั้งค่านี้จะมีผลในครั้งถัดไปที่คุณเปิดหน้าต่างตัวแก้ไข Power Query