function query

4 ขั้นตอนในการสร้าง Function Query แบบที่มี UI ให้แก้เล่นได้ใน Power Query

ในบทความนี้ผมจะมาแนะนำวิธีสร้าง Function Query ขึ้นใน Power Query ซึ่งไม่ใช่แบบปกติ แต่จะเป็นแบบที่ยังยอมให้เราแก้ไข Step ต่างๆ ใน Function นั้นได้โดยง่าย แต่ถ้าใครยังไม่รู้ว่าผมพูดถึงเรื่องอะไร ลองอ่าน recap นี้ดูก่อนเล็กน้อย... Recap Power Query เป็นเครื่องมือสำหรับการดัดแปลงข้อมูลที่ทรงพลังและใช้ง่ายมากๆ อยากจะทำอะไรก็มีเมนูสำเร็จรูปเอาไว้ให้กดได้เลยโดยแทบไม่ต้องเขียนสูตร แต่ว่าหลายครั้งก็น่าเบื่อเวลาที่เราต้องทำ Step อะไรซ้ำๆ เหมือนเดิมกับหลายๆ Query จะดีกว่ามั้ยถ้าเราทำ Step พวกนั้นเป็นฟังก์ชันแล้วเรียกใช้ได้อย่างง่ายๆ เลย? ในหนังสือ Excel Power Up! ที่ผมเขียนไว้ ในบทหลังๆ ก็มีเขียนวิธีที่ทำให้ Query กลายเป็นฟังก์ชันได้ง่ายๆ โดยเปิด Advanced Editor แล้วใส่คำว่า (input1, input2)=> นำหน้า code เดิม ซึ่งมันจะเปลี่ยน…
Power BI ตอนที่ 13: CALCULATE ฟังก์ชันที่ทรงพลังที่สุดใน DAX 1

Power BI ตอนที่ 13: CALCULATE ฟังก์ชันที่ทรงพลังที่สุดใน DAX

ในบทความนี้ผมจะมาพูดถึงฟังก์ชัน CALCULATE ฟังก์ชันที่ทรงพลังที่สุดใน DAX ที่ถูกออกแบบมาให้ทำหน้าที่ "เปลี่ยน Filter Context" โดยเฉพาะ CALCULATE เป็นฟังก์ชันที่หน้าตาดูเรียบง่าย เหมือนไม่มีอะไร แต่ทรงพลังมากๆ ผมว่ามันเทียบได้กับตัวการ์ตูนเอกในเรื่อง One Punch Man ที่ชื่อไซตามะเลยครับ หน้าตาดูไม่เก่งอะไรเลย แต่ต่อยทีเดียวศัตรูตายหมด ดังนั้นอย่าดูถูก CALCULATE เด็ดขาด!! ผมกล้าพูดได้เลยว่าใครที่ใช้ Power BI แต่ไม่รู้จัก CALCULATE ก็เหมือนกับยังใช้ Power BI ไม่เป็นอ่ะครับ ดังนั้นมันสำคัญจริงๆ บอกเลยว่าตั้งแต่บทนี้ไปเนื้อหาจะเริ่มลึกซึ้งมากขึ้นๆ ดังนั้นความรู้เดิมควรจะแน่นก่อน และควรมีสมาธิในการอ่านดีๆ นะครับ อ่านผ่านๆ ไม่คิดตาม ไม่เข้าใจแน่นอน เอาล่ะเราไปเริ่มกันเลย! Recap เล็กน้อย ความหมายของการเปลี่ยน Filter Context ก็จะคล้ายๆ กับในบทความตอนก่อนหน้านี้ที่ ผมได้พยายามเขียนสูตร ALL…
distinct values all

Power BI ตอนที่ 12: DISTINCT, VALUES, ALL และผองเพื่อน

เรื่องของ Table Function ใน DAX นอกจาก FILTER ที่แนะนำไปในบทความที่แล้ว ยังมีที่เราควรจะรู้จักอีก 3 ตัว ก็คือ DISTINCT, VALUES, ALL (และเพื่อนๆ ของมัน) เนื่องจากทั้ง 3 ตัวนี้ทำงานคล้ายกันๆ แต่ไม่เหมือนกัน... ดูเผินๆ แล้วจะสับสนได้ ผมจึงขอทำตารางเปรียบเทียบดังนี้ ฟังก์ชันรองรับ Table/Col ที่เป็นสูตร (Expression)กรณีใช้กับ Tableกรณีใช้กับคอลัมน์เดียวกรณีใช้กับหลายคอลัมน์DISTINCTได้•ได้ Table แบบค่าที่ไม่ซ้ำกัน•ได้ค่าไม่ซ้ำกันในคอลัมน์นั้นทำไม่ได้VALUESไม่ได้•ค่า Duplicates ยังอยู่ •ได้ Blank Row พิเศษ•ได้ค่าไม่ซ้ำกันในคอลัมน์นั้น •ได้ Blank Rowพิเศษทำไม่ได้ALLไม่ได้•ค่า Duplicates ยังอยู่ •ได้ Blank Row พิเศษ •ปลด Filter ออกหมด•ได้ค่าไม่ซ้ำกันในคอลัมน์นั้น •ได้…
Power BI ตอนที่ 11: เรียนรู้ DAX Table Function - FILTER 2

Power BI ตอนที่ 11: เรียนรู้ DAX Table Function – FILTER

ในซีรีส์ Power BI บทก่อนหน้านี้เราได้เรียนรู้จักสูตร DAX พื้นฐานกันไปแล้ว คราวนี้เรามารู้จักฟังก์ชันกลุ่ม Table Function เพิ่มเติม นั่นก็คือ FILTER, DISTINCT, VALUES, ALL ซึ่งจะช่วยให้เราสามารถเขียนสูตร DAX ที่ซับซ้อนได้มากขึ้นในอนาคตนั่นเอง แต่เพื่อไม่ให้มันเยอะเกินไป เดี๋ยวบทความนี้ผมจะพูดถึง FILTER แค่ตัวเดียวก่อน ส่วน ALL, DISTINCT, VALUES และผองเพื่อน จะพูดในตอนถัดไปนะครับ ไฟล์ประกอบ ใช้ไฟล์เดิมmี่ทำมาจากตอนก่อนหน้าได้เลยนะครับ หรือจะใช้อันนี้ก็ได้ Table Function คืออะไร? คำว่า Table Function ก็คือ ฟังก์ชันที่ให้ผลลัพธ์กลับมาเป็น Table หรือตารางนั่นเอง ต้องบอกว่าฟังก์ชันปกติที่เราคุ้นเคยกันก่อนหน้านี้ทุกอัน จะให้คำตอบออกมาเป็นค่าเดียว (เรียกว่า Scalar Value) ไม่ว่าจะเป็นประเภทตัวเลข ตัวหนังสือ logic ก็ตาม…
สูตรสำเร็จรูปสำหรับสร้าง Date Table ด้วย DAX และ Power Query M Code 3

สูตรสำเร็จรูปสำหรับสร้าง Date Table ด้วย DAX และ Power Query M Code

จากที่ผมได้เคยอธิบายไปว่า Date Table มีความสำคัญต่อการทำ Data Model เป็นอย่างยิ่ง โดยเฉพาะการใช้ฟังก์ชัน Date Intelligence ยิ่งจำเป็นต้องใช้ Date Table เลยล่ะ แปลว่า Data Model ของทุกคนควรมี Date Table ซึ่งกลายเป็นเรื่องที่ทุกๆ คนต้องทำเหมือนๆ กันหมด ดังนั้นจะดีกว่ามั้ย ถ้าเราสามารถมีสูตรสำเร็จรูปอะไรซักอย่าง ที่สามารถ Copy Paste ไปใช้สร้าง Date Table ได้เลย? และนั่นคือที่มาของบทความนี้ครับ ทีนี้การสร้าง Date Table นั้นทำได้ 2 วิธี คือใช้ DAX กับใช้ M Code ของ Power Query เรามาดูแต่ละวิธีกันครับ สร้าง Date…
วิธีรวมไฟล์ใน Folder แบบไม่มีปัญหาเรื่องชื่อคอลัมน์ใน Power Query 4

วิธีรวมไฟล์ใน Folder แบบไม่มีปัญหาเรื่องชื่อคอลัมน์ใน Power Query

ปกติเวลาเราสั่งให้ Power Query รวมทุกไฟล์ใน Folder เดียวกัน แม้มันจะเอาข้อมูลรวมกันได้ แต่ก็อาจจะมีปัญหาเรื่องชื่อคอลัมน์ได้ในอนาคต สาเหตุเพราะ มันมีการระบุชื่อคอลัมน์ที่แตกออกมาอย่างชัดเจนในสูตร M Code ทำให้ หากในอนาคตมีคอลัมน์เพิ่มขึ้นหรือลดลง ก็จะมีปัญหาทันที (อย่างเช่นที่ผมบอกไว้ในหนังสือ Excel Power Up ว่า เมื่อไหร่ก็ตามที่มีการระบุชื่อคอลัมน์ในสูตร M Code นั้น จะอันตรายต่อการ Refresh ทันที) ลองทำดู โหลดไฟล์ประกอบได้ที่นี่ ตัวอย่างเช่น ผมสั่งรวมข้อมูลจาก Folder ที่ชื่อ MyFolder และสร้าง Custom Column ดังนี้ เพื่อดึงข้อมูลจาก Excel ด้วยสูตรว่า =Excel.Workbook([Content],true) ผมแตกคอลัมน์ Custom ออกมาจะได้ดังนี้ เดี๋ยวผมลบคอลัมน์ที่ไม่ใช้ทิ้งก่อน โดยจะเก็บแค่ ชื่อไฟล์ ชื่อ Item…
การใช้ Power Query จัดการข้อมูลที่มีหัวตาราง 2 ชั้น 5

การใช้ Power Query จัดการข้อมูลที่มีหัวตาราง 2 ชั้น

ปัญหาข้อมูลเน่าแบบ classic อันนึงเลยก็คือ ได้ข้อมูลเชิง Report มา และดันใส่หัวตาราง 2 ชั้นมาด้วย ทำให้ใช้ Unpivot ตรงๆ ไม่ได้ คราวนี้จะทำยังไงดี มาดูกันครับ Download ข้อมูลที่มีหัวตาราง 2 ชั้นได้ที่เว็บสำนักงานสถิติแห่งชาติ จำนวนนักเรียน นิสิต นักศึกษาในระบบโรงเรียน ในสถานศึกษาของรัฐบาลและเอกชน จำแนกตามระดับการศึกษา และชั้น ปีการศึกษา 2556 - 2560 หรือ http://statbbi.nso.go.th/staticreport/Page/sector/TH/report/sector_03_3_TH_.xlsx หลักๆ การจัดการข้อมูลใน Power Query จะมีแนวทางดังนี้ ข้อมูลอยู่ในรูปแบบ Report ยังไม่ใช่ข้อมูลเชิง Database ติดข้อมูลอื่นที่ไม่เกี่ยวมา เช่น header/footer -> filter, remove col, remove top/bottom rowsข้อมูลเว้นว่างแบบละไว้ในฐานที่เข้าใจ…
เทคนิคการเผชิญหน้ากับข้อมูลเน่าเฟะด้วย Power Query 6

เทคนิคการเผชิญหน้ากับข้อมูลเน่าเฟะด้วย Power Query

หากคุณกำลังเจอข้อมูลเน่าเฟะ อย่าเพิ่งสิ้นหวัง... ทำอย่างไรเมื่อเจอข้อมูลที่อยู่ในรูปแบบที่แย่และไม่ ok เอาซะเลย และดันมีความจำเป็นต้องแก้ด้วย Power Query ซะด้วยสิ ในบทความนี้เราจะมาเผชิญความเน่าเฟะไปด้วยกันครับ เตือนไว้ก่อนว่าบทความนี้ยาวหน่อยและมีหลายขั้นตอน ซึ่งเป็นเรื่องปกติที่การกำจัดข้อมูลที่เน่าเฟะนั้นต้องใช้เวลา ดังนั้นอย่าเพิ่งท้อถอยนะครับ รับรองว่าจะได้ความรู้ดีๆ กลับไปแน่นอน ข้อมูลเน่าเฟะที่ได้มา สมมติว่าเราได้ข้อมูลหุ้นในตลาด SET100 ทุกตัวหน้าตาแบบนี้มา ซึ่งผมใช้ Python ดึงมารวมกันแบบไม่ได้ Clean ดีๆ ตั้งแต่แรก (จะได้มาฝึกทำใน Power Query ไง 555) ซึ่งดาวน์โหลดไฟล์ได้ที่นี่ (Google Drive / GitHub) ความยาก1 มี Topic แทรกอยู่ระหว่างบรรทัดอยู่เป็นระยะๆ เช่น ทรัพย์สินรวม หนี้สินรวม ความยาก2 จำนวนตัวเลขต่อ 1 Topic มี 4 ตัวบ้าง 5…
Power BI ตอนที่ 10: เรียนรู้ DAX เบื้องต้น 7

Power BI ตอนที่ 10: เรียนรู้ DAX เบื้องต้น

ในบทความนี้เราจะมาเรียนรู้การใช้ DAX เบื้องต้นกันครับ เดี๋ยวจะสอนแบบที่ว่า ไม่เคยใช้ DAX เลยก็สามารถเข้าใจได้นะ ก่อนอื่นเรามาลองดู Data ของตารางหลัก นั่นก็คือ fSales กันก่อนครับ เกิดอะไรขึ้นเมื่อลาก Field ตัวเลขลง Visual สิ่งที่เราควรรู้คือ เราสามารถลาก Field ที่เป็นตัวเลข (สังเกตว่าที่ Field มีสัญลักษณ์ Sigma อยู่) อย่างเช่น SalesQuantity ลง Visual ได้เลย และเราก็สามารถเลือกวิธีสรุปข้อมูลได้เช่นจะให้ Sum, Count, Average, Max, Min หรือแม้แต่ Distinct Count เวลาใน Values เรากดที่ลูกศรลงของ SalesQuantity ก็จะเห็นวิธีสรุปที่เลือกได้มากมายดังนี้ ค่า Default ของการลากข้อมูลตัวเลขลงมาก็คือการ Sum แต่เราสามารถเปลี่ยนวิธีการสรุปข้อมูลได้ตาม…
Power BI ตอนที่ 09: สร้าง Date Table ด้วย DAX 8

Power BI ตอนที่ 09: สร้าง Date Table ด้วย DAX

ในตอนที่แล้วเราได้สร้าง Data Model ให้เป็น Star Schema ได้แล้ว เหลืออีกขั้นตอนนึงในการทำให้ Data Model ของเราสมบูรณ์ยิ่งขึ้น นั่นก็คือการสร้าง Date Table นั่นเอง แม้ว่าในความเป็นจริงแล้วเราสามารถลาก Field วันที่จาก Fact Table ลงไปใน Visual ได้เลย แต่ทว่านั่นเป็นสิ่งที่เหล่าผู้เชี่ยวชาญไม่แนะนำให้ทำ เพราะมันจะสร้างตารางวันที่จำลองที่มองไม่เห็นขึ้นมาเต็มไปหมด วิธีที่ถูกต้องคือสร้าง Dimension Table ที่เป็นตารางวันที่ขึ้นมาต่างหาก การสร้าง Date Table นอกจากจะลดปัญหาที่บอกข้างบนแล้ว ยังช่วยให้ฟังก์ชัน DAX ตระกูล Time Intelligence ทั้งหมดทำงานได้อย่างถูกต้องอีกด้วย เช่นการคิดเลขสะสมตั้งแต่ต้นปี (Year-to-Date) หรือ การคิดเทียบกับช่วงเดียวกันในปีที่แล้ว (Same period last year) แต่ว่าการเขียนสูตรพวก Time Intelligence…