Month: March 2020

วิธีสร้างเลข Combination กัน

มีคนถามมาว่า ถ้ามีเลข 0, 2, 3, 6, 9 สามารถผสมเป็นเลข 2 Digit ได้แบบไหนบ้าง? ผมว่าน่าสนใจดีเลยเอามาทำบทความให้ครับ วิธีทำที่ผมคิดว่าง่าย ก็คือใช้ Power Query ช่วยทำ ซึ่งมีวิธีทำดังนี้

เริ่มต้นสร้างเลข Combination

ก่อนอื่นสร้าง Table ที่มี List ตัวเลขที่ต้องการก่อน

แล้วก็เอาเข้า Power Query ซะ แล้วเปลี่ยน Data Type เป็น Text เพื่อความยืดหยุ่น (กดแบบ Replace Current)

จากนั้น Add Custom Column ขึ้นมาแล้วใส่สูตรว่า =1 เพื่อให้ออกมาเป็นเลข 1 ทั้งหมด

จากนั้นทำการ Merge Query กับตัวเองซะ เลือกตัวเชื่อมเป้นคอลัมน์ที่มีเลข 1 ที่เราสร้างไว้

กด Expand ผลลัพธ์ออกมา

จากนั้นเลือกคอลัมน์ Num กับ Num.1 แล้วสั่ง Merge กันซะ จะใส่ตัวคั่นรึเปล่าก็แล้วแต่

จากนั้นลบ Column Custom ออกไปก็จบละ กด Load เอาผลลัพธ์ออกมาได้เลย

ลองเปลี่ยนข้อมูลดูได้ ใช้ตัวหนังสือก็ได้

March 31, 2020
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 เดิม

ซึ่งมันจะเปลี่ยน Query นั้นให้กลายเป็นฟังก์ชันที่มี input 2 ตัวคือ input1 กับ input2 ทันที แต่ข้อเสียคือถ้าจะแก้ต้องมาแก้ใน Advanced Editor ไม่สามารถมี User Interface ง่ายๆ ให้แก้อีกแล้ว

ในบทความนี้ผมก็เลยจะมาแชร์วิธีสร้าง Function อีกวิธีนึงซึ่งยังสามารถทำให้เราแก้ไขได้ง่ายๆ โดยใช้ User Interface ได้อยู่ โดยที่ผมเห็นวิธีนี้ครั้งแรกมาจากคุณโบแห่ง Excel Wizard ที่ไปตอบในคลิปของ อ. Mike แห่ง Excel is fun

ผมเห็นว่ามันเป็นประโยชน์มาก เลยขออนุญาตนำเทคนิคนี้มาถ่ายทอดอีกที แต่มีการดัดแปลงเล็กน้อยให้เข้าใจง่ายขึ้น (ล่ะมั๊ง) นะครับ 555

วิธีใช้ Function Query แบบแก้ไขได้

หลักการมี 4 ขั้นตอน คือ
1. สร้าง New Parameter ขึ้นมา โดยจะสร้างกี่ตัวก็ได้ สมมติชื่อ Para1, Para2
2. เอา Parameter จากข้อ 1 ไปเรียกใช้ใน Query ที่ต้องการจะให้เป็นต้นแบบ (อันนี้ต้อง Manual แก้สูตรนิดนึง) สมมติชื่อ StepQuery ซึ่ง Function ของเราจะทำ Step ตาม Query ตัวนี้เลย
3. สร้างฟังก์ชัน โดยกดคลิ๊กขวา ที่ StepQuery แล้ว Create Function แล้วตั้งชื่อฟังก์ชันอะไรก็ได้
4. นำฟังก์ชันไปใช้งาน เช่นเอาไปใช้ใน Query หลัก อาจใช้ผ่าน Invoke Custom Function ก็ได้ง่ายดี
ซึ่งแบบที่เข้าใจง่ายที่สุดก็คือ Parameter ที่เป็นพวกค่าเดี่ยวๆ เช่น ตัวเลข หรือ ข้อความ เป็นต้น

ตัวอย่างของการใช้ Parameter แบบนี้แล้วมีประโยชน์มากก็คือ การดึงข้อมูลจากเว็บไซต์ต่างๆ โดยเปลี่ยน URL ไปเรื่อยๆ เช่น การดึงข้อมูลหุ้น เป็นต้น

ตัวอย่างการสร้าง Function ดึงข้อมูลหุ้นจาก symbol ที่ต้องการ

สมมติผมต้องการข้อมูลจากเว็บหน้านี้ https://www.set.or.th/set/companyhighlight.do?symbol=SCC

ผมก็สามารถสั่ง Get Data From Web ได้ โดยเลือกว่าจะดูด table ในรูป

ที่นี้มันจะดูดข้อมูลเข้า Power Query เฉพาะ Symbol นี้อันเดียว

ให้เปลี่ยนชื่อ Query เป็น StepQuery ซะ เพราะตารางนี้แหละที่เราจะใช้เป็นแม่เแบบของการ Transform Data ซึ่งเราจะ Transform ข้อมูลให้เสร็จไปเลยก็ได้ แต่ผมยังไม่ทำ เพราะไปทีหลังได้

1. สร้าง Parameter

ทีนี้ให้เราสร้าง Parameter ขึ้นมา โดยกด Manage Parameter แล้ว New แล้วตั้งชื่อว่า StockSymbol จากนั้นเลือก Type เป็น Text เพราะว่า Parameter นี้คือข้อความ จากนั้นใส่ Current Value เป็น SCC ไปก่อน เพื่อจะได้ดึงค่าจากหุ้น SCC ได้

2.เอา Parameter ไปใช้ใน Step Query

กดตามรูป แล้วไปแก้สูตรของ Source ซะ ให้เปลี่ยนจากข้อความว่า SCC เป็น Parameter ที่ชื่อว่า StockSymbol แทน ซึ่งเชื่อมด้วยเครื่องหมาย &
```
= Web.Page(Web.Contents("https://www.set.or.th/set/companyhighlight.do?symbol="&StockSymbol))
```
พอลองกดกลับมาดู Step หลังๆ ก็ควรจะต้องเห็นว่ายังใช้งานได้อยู่เหมือนเดิม

3.สร้างฟังก์ชัน

จากนั้นคลิ๊กขวาที StepQuery แล้วกด Create Function

จากนั้นตั้งชื่อตามใจชอบเช่น GetStockData แล้วกด ok

มันจะสร้างฟังก์ชันแล้วผูก Query ให้เราโดยอัตโนมัติ

4.นำฟังก์ชันไปใช้งาน

ทีนี้เราจะลองใช้งานเพื่อดูดหุ้นซัก 5-6 ตัว แต่เนื่องจากเรายังไม่มี List รายชื่อหุ้นเลย ดังนั้นเดี๋ยวไปสร้าง List รายชื่อหุ้นก่อน ซึ่งเราจะสร้างใน Excel ไฟล์นี้แหละ

ดังนั้นก่อนอื่นให้เรา Close & Load To… -> Only Create Connection เพื่อออกไปยัง Excel ก่อน (ไม่งั้นมันไม่ให้ออกไปสร้าง)

ให้เราใส่ Data ลงไปตามต้องการ แล้วกดสร้าง Table ชื่อ StockList ซะ

จากนั้นกด Get Data From Table/Range เพื่อเอาเข้า Power Query

จากนั้นกด Add Column -> Invoke Custom Function เพื่อเรียกใช้ฟังก์ชันที่เราสร้างขึ้นมา

มันอาจจะมีขึ้นมาเตือนเรื่อง Data Privacy เพราะมีการดึงข้อมูลจาก Local ผสมกับจากแหล่งออนไลน์ ก็ให้กด Continue ไป

จุดนี้ถ้าไม่คิดอะไรมาก ติ๊ก Ignore Privacy Level ก็ได้ (มันก็จะไม่แคร์เรื่องข้อมูลรั่วไหลอะไรเลย)

จากนั้นมันก็จะดึงข้อมูล Symbol ทุกตัวออกมาเป็น Table ดังรูป เมื่อคลิ๊กแต่ละช่องที่เป็น Table เราก็จะเห็นไส้ในของ Table นั้น

ถ้าเรากด Expand ที่มุมขวาบนของคอลัมน์ มันก็จะเอาทุก Table มา Append ต่อแถวกันโดยอัตโนมัติ โดยการ Append ก็จะยึดจากชื่อคอลัมน์ที่เป็นหัวตารางเป็นหลัก

แต่เนื่องจากหัวตารางไส้ในของเรายังไม่ ok ดังนั้นเราจะกลับไปแก้ StepQuery ให้เรียบร้อยก่อน

กลับไปแก้ StepQuery (เพราะเราข้ามมา)

เอาล่ะ เรากดย้อนหลับมายัง StepQuery เพื่อจัดการตารางให้เรียบร้อยกัน

ก่อนอื่นจะ Filter เอาบรรทัดที่ไม่เกี่ยวข้องออก นั่นก็คือคอลัมน์แรกจะเอาตัวที่มีคำว่า “สำคัญ” ออกไปซะ

ให้ Transpose ตารางลงมาก่อน

แล้ว Use First Row as Header ซะ แต่จะลบ Step Change Type ออกเพื่อไม่ให้มันระบุชื่อหัวตารางในสูตร

จากนั้นเราอยากใส่ลำดับปีเป็น Y-4, Y-3, Y-2, Y-1, Y-0 ดังนั้นจะกด Add Index Column ใหม่แบบ Custom

จากนั้นจะได้เลขเรียง 4,3,2,1,0 ออกมา

แต่คราวนี้เราจะใส่คำว่า Y- ไว้ข้างหน้า ก็ไปที่ Transform -> Format-> Add Prefix

ก็จะได้ผลลัพธ์ตามต้องการ

กลับไปดูผลที่ Query หลัก (หลังจากแก้ StepQuery)

เมื่อกดกลับไปดูที่ StockList ซึ่งเป็น Query หลักของเรา จะเห็นว่าที่เราไปแก้ใน StepQuery นั้นได้สะท้อนออกมายัง Query หลักแล้ว

คราวนี้เราก็ Expand ข้อมูลได้อย่างสบายใจแล้ว

แค่นี้เราก็จะได้ข้อมูลจากทุก Symbol มารวมกันแล้วครับ

ถ้าใครไม่อยากให้ตอน Expand มันระบุชื่อคอลัมน์ ก็สามารถทำตาม Tips ของบทความนี้ได้ เพื่อให้มันอ่านหัวคอลัมน์ใหม่ได้ตลอด

เช่น เราจะแก้สูตรเป็น
```
= Table.ExpandTableColumn(#"Invoked Custom Function", "GetStockData", List.Union(List.Transform(#"Invoked Custom Function"[GetStockData],each Table.ColumnNames(_))))
```
ผลลัพธ์ที่ได้จะเหมือนเดิม แต่จะยืดหยุ่นขึ้นกรณีที่ในอนาคตอาจจะมี Field เพิ่มก็จะเห็นทันที

ต่อจากนี้จะเปลี่ยน Datatype ตัวไหนยังไงก็แล้วแต่เลยครับ ผมขอไม่ทำให้ดูแล้วนะ ^^

ตอนต่อไปคืออะไร?

ในบทความตอนถัดไป จะเป็นวิธีสร้างฟังก์ชันคล้ายๆ แบบบทความนี้ แต่สร้าง Parameter เป็น Any แทนที่จะเป็น Parameter แค่ Text ธรรมดา เพื่อให้ดึงข้อมูลจาก Table ที่ต้องการได้ ซึ่งจะยากขึ้นอีกเล็กน้อยครับ

ซึ่งแบบหลังนี้แหละที่คุณโบ Excel Wizard ทำให้ดูในคลิป Excel is fun แต่ถ้าให้ผมอธิบายวิธีนั้นเลย น่าจะงงแน่นอน เลยต้องสอนแบบง่ายก่อนนะ

แล้วพบกันในตอนต่อไปครับ รับรองว่าเจ๋งมากๆ อีกแล้ววว
March 28, 2020
Power BI ตอนที่ 13: CALCULATE ฟังก์ชันที่ทรงพลังที่สุดใน DAX
ในบทความนี้ผมจะมาพูดถึงฟังก์ชัน CALCULATE ฟังก์ชันที่ทรงพลังที่สุดใน DAX ที่ถูกออกแบบมาให้ทำหน้าที่ “เปลี่ยน Filter Context” โดยเฉพาะ

CALCULATE เป็นฟังก์ชันที่หน้าตาดูเรียบง่าย เหมือนไม่มีอะไร แต่ทรงพลังมากๆ ผมว่ามันเทียบได้กับตัวการ์ตูนเอกในเรื่อง One Punch Man ที่ชื่อไซตามะเลยครับ หน้าตาดูไม่เก่งอะไรเลย แต่ต่อยทีเดียวศัตรูตายหมด ดังนั้นอย่าดูถูก CALCULATE เด็ดขาด!!

ผมกล้าพูดได้เลยว่าใครที่ใช้ Power BI แต่ไม่รู้จัก CALCULATE ก็เหมือนกับยังใช้ Power BI ไม่เป็นอ่ะครับ ดังนั้นมันสำคัญจริงๆ

บอกเลยว่าตั้งแต่บทนี้ไปเนื้อหาจะเริ่มลึกซึ้งมากขึ้นๆ ดังนั้นความรู้เดิมควรจะแน่นก่อน และควรมีสมาธิในการอ่านดีๆ นะครับ อ่านผ่านๆ ไม่คิดตาม ไม่เข้าใจแน่นอน

ไฟล์ประกอบ

ใช้ไฟล์เดิมจากตอนก่อนหน้าได้เลยนะครับ หรือจะใช้อันนี้ก็ได้

เอาล่ะเราไปเริ่มกันเลย!

Recap เล็กน้อย

ความหมายของการเปลี่ยน Filter Context ก็จะคล้ายๆ กับในบทความตอนก่อนหน้านี้ที่ ผมได้พยายามเขียนสูตร ALL เพื่อปลด Filter Context เดิมออกแล้ว Filter สินค้าที่เป็น Class ระดับ Economy เข้าไปอีกที ด้วยสูตรว่า
```
FilterEconomyRevenue =
SUMX (
    FILTER ( ALL ( fSales ), RELATED ( dProduct[ClassName] ) = "Economy" ),
    fSales[SalesQuantity] * RELATED ( dProduct[UnitPrice] )
)
```
ถ้าหากเราใช้ CALCULATE ทำ เราสามารถเขียนสูตรสั้นๆ เหลือแค่นี้
```
CalculateEconomyRevenue = 
CALCULATE([TotalRevenue],dProduct[ClassName]="Economy")
```
จะเห็นว่า CALCULATE นั้นสามารถใช้สูตรที่ทั้งสั้นกว่า และดูน่าจะอ่านเข้าใจง่ายกว่าสูตรยาวๆ นั้นด้วย ดังนั้นเรามาดูกันดีกว่าว่า CALCULATE มีวิธีการใช้งานยังไงกันแน่

ทำความเข้าใจ CALCULATE
```
CALCULATE ( <Expression> , [<Filter1>] , [<Filter2>] , ... )
```
CALCULATE นั้นประกอบไปด้วย 2 ส่วนหลักๆ ก็คือ
- Expression หรือก็คือวิธีการคำนวณ เราจะใส่สูตรคำนวณเอาเองหรือจะอ้างอิง Measure ที่มีอยู่แล้วก็ได้
- Filter คือ Filter Context ใหม่ที่อยากได้ สามารถใส่ได้หลายตัวโดยคั่นด้วย comma ซึ่งจะเป็นเงื่อนไขแบบ AND
  - สามารถใส่เป็นเงื่อนไขเปรียบเทียบคล้ายๆ กับเงื่อนไขในฟังก์ชัน FILTER ได้เลย
    เช่น Field เทียบกับค่าอะไรบางอย่าง
    ซึ่งความหมายคือจะมีการปลด Filter เดิมของ Field นั้นออกด้วย ALL แล้วค่อยใส่ Filter ใหม่ตามที่เราระบุ
  - ความพิเศษคือสามารถอ้างอิง Field จากตารางไหนก็ได้ โดยไม่ต้องใช้ RELATED มาช่วยเลย
หมายเหตุ : CALCULATE จะประเมิน Filter ก่อน Expression เสมอ

ฟังดูแล้วอาจจะยังงงๆ งั้นมาดูคำอธิบายของตัวอย่างข้างบนกัน
```
CalculateEconomyRevenue = CALCULATE([TotalRevenue],dProduct[ClassName]="Economy")
```
- Expression เขียนว่า [TotalRevenue] นั่นคือให้ใช้สูตรวิธีคำนวณของ Measure ที่ชื่อว่า [TotalRevenue] มาคำนวณ
- Filter เขียนว่า dProduct[ClassName]=”Economy” ความหมายคือ ให้สั่งปลด Filter ของ Field dProduct[ClassName] ออกก่อนด้วย ALL จากนั้นค่อย Filter dProduct[ClassName] ให้เป็น Economy อีกที
ก่อนจะมีการใช้ CALCULATE

Total Revenue จะถูก Filter ตามแต่ละ ClassName อย่างที่ผมเคยอธิบายไปในบทแรกๆ ว่าแต่ละจุดของตาราง Pivot นั้นมี Filter Context ที่ไม่เหมือนกันเลย

หลังจากใช้ CALCULATE

มันปลด Filter ของ ClassName ออกหมด แล้ว Filter เข้าไปใหม่ด้วย ClassName ว่าต้องเท่ากับ Economy ทุกตัวเลยได้ค่าเดียวกันหมดเลยดังรูป

สรุปสั้นๆ นะ

“CALCULATE จะเปลี่ยน Filter ตามเงื่อนไขที่ระบุ แล้วค่อยคำนวณตามวิธีที่เราระบุ“

โดยที่ถ้าใสเงื่อนไขแบบปกติมาตรฐาน เช่น Field เทียบกับค่าอะไรบางอย่าง มันก็จะปลด Filter เดิมของ Field นั้นๆ ออกไป แล้วค่อยใส่ Filter ใหม่ตามที่เราระบุในส่วน Filter ของ CALCULATE

จากคำอธิบายข้างบน จริงๆ แล้วสูตร CALCULATE ข้างบน มันคือการเขียนโดยย่อของการเขียนแบบนี้เลยครับ เทียบเท่ากันทุกประการ!!
```
CalculateEconomyRevenueFull =
CALCULATE (
    [TotalRevenue],
    FILTER ( ALL ( dProduct[ClassName] ), dProduct[ClassName] = "Economy" )
)
```
ดังนั้นแปลว่า ถ้าผมใส่ Filter ตัวอื่นที่ไม่เคยถูก Filter มาก่อน เช่น StoreType ด้วยสูตรนี้ว่าต้องการ StoreType แบบ Online เท่านั้น
```
CalculateOnlineRevenue = 
CALCULATE([TotalRevenue],dStores[StoreType]="online")
```
การทำงานก็ยังคงเป็น Concept เดิม คือ ปลด Filter ด้วย ALL แล้ว Filter เข้าไปด้วยตัวใหม่ที่ระบุ

ผลที่ได้ก็จะเป็นการ Filter เพิ่มจากตัว [TotalRevenue] ปกติ เนื่องจากปลด Filter dStores[StoreType] ก็ไม่ได้มีผลอะไร (เพราะไม่ได้มี Filter ไว้อยู่แล้ว) จากนั้นค่อย Filter dStores[StoreType] ให้เป็น Online

ทั้งนี้มันต่างจากตอนแรกที่เราสั่ง dProduct[ClassName] = “Economy” ในแง่ที่ว่าอันนั้นการปลด Filter ออกจะเห็นผลที่ชัดเจนเลย เพราะถ้าไม่ปลดออก กรณี Class Name ใน Visual ไม่ใช่ Economy จะต้องได้ค่า Blank เนื่องจาก ไม่มีทางที่จะเป็น Class Name 2 อย่างได้พร้อมกันอยู่แล้ว

CALCULATE หลายเงื่อนไข

เงื่อนไขแบบ AND

ถ้าเราใส่ Filter สองเงื่อนไขคั่นด้วย comma มันจะเป็นเงื่อนไขแบบ AND เลย จะเป็นยังไงมาดูกันครับ
```
CalculateOnlineEconomyRevenue =
CALCULATE (
    [TotalRevenue],
    dProduct[ClassName] = "Economy",
    dStores[StoreType] = "online"
)
```
ผลที่ได้จะเป็นการปลด Filter ClassName และ StoreType ออกทั้งหมด และสั่งให้ Filter ClassName เป็น Economy และ StoreType เป็น online นั่นเอง

และถ้าจะเป็นเงื่อนไขแบบ OR ล่ะ?

OR Field เดียวกัน ไม่มีปัญหา

ถ้าเป็นเงื่อนไขแบบ OR แบบอ้างอิง Field เดียวกันเรายังพอใช้เครื่องหมาย || หรือพวก IN มาช่วยได้ดังนี้
```
CalculateEconomyRegular =
CALCULATE (
    [TotalRevenue],
    dProduct[ClassName] = "Economy"
        || dProduct[ClassName] = "Regular"
)
```
หรือ
```
CalculateEconomyRegular =
CALCULATE ( [TotalRevenue], dProduct[ClassName] IN { "Economy", "Regular" } )
```
OR คนละ Field เริ่มยากละ

แต่ถ้าเราจะอ้างอิง Field คนละ Field กันแบบ OR มันจะไม่ยอม เช่น

ดังนั้นเราจะใช้ CALCULATE เปล่าๆ ไม่ได้แล้ว ต้องใช้ฟังก์ชันอื่นมาช่วย นั่นก็คือ FILTER กับ ALL มาช่วยนั่นเอง เช่น
```
CalculateEconomy_or_Online =
CALCULATE (
    [TotalRevenue],
    FILTER (
        ALL ( fSales ),
        RELATED ( dProduct[ClassName] ) = "Economy"
            || RELATED ( dStores[StoreType] ) = "online"
    )
)
```
สูตรยากกว่า CALCULATE สำเร็จรูปเยอะเลยเนอะ…

ซึ่งจะเห็นว่าในบางสถานการณ์ ความรู้เรื่อง FILTER และ ALL ที่ผมสอนในบทก่อนๆ ก็จำเป็น ไม่งั้นเราจะเขียนสูตรบางอย่างไม่ได้เลย เพราะ CALCULATE สำเร็จรูปไม่ได้รองรับทุกอย่างไปหมดหรอกครับ

เราน่าจะพอเห็นภาพการทำงานของ CALCULATE กันแล้ว คราวนี้ลองมาดูกันว่าถ้าใส่ CALCULATE ซ้อนกันจะเกิดอะไรขึ้น

CALCULATE ซ้อนกัน

สมมติผมเขียน Measure แบบนี้ ให้ทายว่ามันจะเปลี่ยน Filter ให้กลายเป็นแบบไหน?
```
Calculate2X =
CALCULATE (
    CALCULATE (
        [TotalRevenue],
        dProduct[ClassName] = "Economy",
        dStores[StoreType] = "online"
    ),
    dProduct[ClassName] = "Regular"
)
```
ถ้าดูเผินๆ คนทั่วไปน่าจะคิดว่า มันน่าจะได้เป็น Online และ Regular แต่ในความเป็นจริง Filter ที่หลงเหลือกลับได้เป็น Online และ Economy เหมือนเดิม!!

ทำไมถึงเป็นแบบนั้น? ทั้งๆ ที่การเขียนสูตร 2 ชั้นซ้อนกันมันต้องคำนวณตัวข้างในก่อนสิ ?

การเขียนสูตร 2 ชั้นจะคำนวณตัวในก่อนนั้นถูกต้องแล้วครับ แต่สาเหตุที่ผลลัพธ์เป็นแบบนี้ก็เพราะ CALCULATE จะประเมิน Filter ก่อน Expression เสมอครับ

มาดูกันให้ชัดๆ ว่าเกิดอะไรขึ้น
```
Calculate2X =
CALCULATE (
CALCULATE (
[TotalRevenue],
dProduct[ClassName] = "Economy",
dStores[StoreType] = "online"
),
dProduct[ClassName] = "Regular"
)
```
CALCULATE ตัวข้างใน (สีแดง) จะถูกคำนวณก่อน ซึ่งเดี๋ยวมันจะต้องคิดว่าจะต้องเปลี่ยน Filter ยังไงดี ทำให้มันต้องปลด Filter ClassName และ StoreType ออกให้หมดก่อน ทำให้ dProduct[ClassName] = “Regular” ที่อยู่ในชั้นนอกถูกทำลายไปโดยปริยาย

จากนั้นมันค่อยใส่ Filter ไปใหม่ว่า ClassName ให้เป็น Economy และ StoreType เป็น online นั่นเอง

กลายเป็นว่า เวลาใช้ CALCULATE ซ้อนกันหลายชั้น ชั้นในดันได้รับ Priority เรื่อง Filter มากว่านั่นเอง และจะทำลายชั้นนอกตัวที่ซ้ำกันด้านในทิ้งหมดเลย

เดี๋ยวลองเขียนเงื่อนไขชั้นนอกอีกอันแบบไม่ซ้ำกับข้างในบ้าง
```
Calculate2X_v2 =
CALCULATE (
    CALCULATE (
        [TotalRevenue],
        dProduct[ClassName] = "Economy",
        dStores[StoreType] = "online"
    ),
    dProduct[ClassName] = "Regular",
    dDate[Year] = 2012
)
```
จะเห็นว่า Filter ที่หลงเหลือก็คือ Online และ Economy และ ปี 2012 เพราะว่าเลขลดลงไปกว่า 2x ปกติ ดังนั้นจะเห็นว่า เงื่อนไข Filter ของ CALCULATE ชั้นนอกไม่ได้ถูกทำลายไปทั้งหมด แต่ถูกทำลายเฉพาะตัวที่ซ้ำกับด้านในเท่านั้น

หลายคนอาจเริ่มสงสัยแล้วว่า “ในชีวิตจริงใครจะไปเขียน CALCULATE 2 ตัวซ้อนกัน งงจะตายชัก??”

ครับ มันไม่มีใครตั้งใจเขียนแบบนั้นหรอก แต่เป็นเพราะความลับอีก 2 อย่างต่อจากนี้ต่างหากที่ทำให้เราต้องระวังเรื่องนี้ นั่นก็คือ…
- CALCULATE สามารถเปลี่ยน Row Context ให้เป็น Filter Context ได้ เรียกว่า Context Transition
- Measure ทุกตัวมี CALCULATE แฝงอยู่ข้างในเสมอ
ซึ่งเดี๋ยวผมจะพูดถึง 2 เรื่องนี้ในบทถัดไปครับ ซึ่งบอกเลยว่าเป็น Concept ของ CALCULATE ที่ลึกลับซับซ้อนที่สุดที่คนส่วนใหญ่ล้วนงงไปตามๆ กันเลยล่ะ!!

สารบัญ Series Power BI
ใครสนใจอยากเรียนเป็นคลิปวีดีโอ ผมมี
March 27, 2020

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 พิเศษ •เห็นทุกค่าเสมอ	•ได้ค่าไม่ซ้ำกันในคอลัมน์นั้น •ได้ Blank Row พิเศษ •เห็นทุกค่าเสมอ	•ได้ค่าไม่ซ้ำกันเมื่อมองผสมหลายคอลัมน์ •ได้ Blank Row พิเศษ •เห็นทุกค่าเสมอ

หมายเหตุ

Blank Row พิเศษ จะโผล่มากรณีที่ Relationship ไปเจอข้อมูลเป็น Key ในฝั่ง Many แต่ดันไม่มีใน Key ฝั่ง One (จะมีอธิบายในบทความด้านล่าง)
ALLSELECTED = เหมือน All แต่ปลดแค่ Filter ใน Visual นั้นๆ

ไฟล์ประกอบ

ใช้ไฟล์เดิมจากตอนก่อนหน้าได้เลยนะครับ หรือจะใช้อันนี้ก็ได้

สร้าง New Table ใหม่

เพื่อให้เห็นภาพมากขึ้น เดี๋ยวเราจะสร้างตารางใหม่ที่ชื่อว่า dStoresNew โดยจะเอาให้เหลือเฉพาะ StoreKey ตั้งแต่ 100 ขึ้นไปเท่านั้น (เพื่อตั้งใจสร้างสถานการณ์ให้เจอ Blank Row พิเศษครับ)

ให้เรา NewTable ขึ้นมาดังนี้

dStoresNew = FILTER(dStores,dStores[StoreKey]>100)

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

เราจะได้ dStoresNew ที่มี 207 Rows นะครับ

จากนั้นให้ผูก Relationship กับ Fact ที่เป็น fSales ด้วย

เมื่อเตรียมข้อมูลพร้อมแล้ว มาดูรายละเอียดทีละตัวกันครับ

DISTINCT

“จะได้ item แบบไม่ซ้ำกัน”

DISTINCT ( <ColumnNameOrTableExpr> )

DISTINCT นั้นจะใช้กับ Table ก็ได้ หรือจะใช้กับ คอลัมน์เดียวก็ได้ จะได้ข้อมูลแบบไม่ซ้ำกัน

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

*จะเห็นว่า DISTINCT ไม่มี Blank Row พิเศษโผล่ออกมาเหมือนกับ VALUES และ ALL ที่จะแสดงให้เห็นถัดไป

และสามารถใส่เป็นสูตรได้ด้วย (รองรับ input แบบ Expression) ตัวอย่างเช่น

DistinctTableExpression = DISTINCT(FILTER(dProduct,dProduct[BrandName]="fabrikam"))

VALUES

“มี Blank row พิเศษ โดยถ้าใช้กับคอลัมน์จะได้ item แบบไม่ซ้ำกัน ถ้าใช้กับตารางจะไม่ได้เอาตัวซ้ำออกให้”

VALUES ( <TableNameOrColumnName> )

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

สังเกตว่าจะได้ Blank Row พิเศษติดมาด้วย ซึ่งจะเกิดขึ้นกรณีที่อีกฝั่งของตารางที่มันอ้างอิง เช่น fSales ดันมี StoreKey ที่ไม่มีในตารางนี้ด้วย (ที่เลข StoreKey <100)

เจ้า Blank Row พิเศษนี้ถูกสร้างขึ้นมาเพื่อจับคู่กับ key ตัวที่เหลือในฝั่ง fSales ที่หาคู่ใน dStoresNew ไม่ได้นั่นเอง

ดังนั้นสรุปได้ว่าเวลาใช้งานส่วนใหญ่ จะมองว่าใช้ VALUES กับคอลัมน์จะให้ผลคล้าย DISTINCT แต่ได้ Blank row พิเศษมาด้วย

และ VALUES ไม่รองรับ Table/Column Expression นะครับ ให้ input เป็นสูตรไม่ได้เลย มันจะไม่ยอม

ALL

ALL เป็นตัวที่ผมอยากลงรายละเอียดมากที่สุด เพราะเราจะได้เจอมันค่อนข้างบ่อยเลย

“การทำงานเหมือน VALUES แต่จะมองเห็นครบทุกค่าเสมอ นอกจากนี้ยังอ้างอิงคอลัมน์อันเดียว หรือ หลายๆ อันก็ได้ (แต่ต้องตารางเดียวกัน)”

กรณีอ้างอิงทั้งตาราง

“จะเห็นครบทุกค่า ถ้าใช้กับตารางจะไม่ได้เอาตัวซ้ำออก และก็มี Blank row พิเศษ“

ALL ( TableName )

เช่น

AllTable1 = ALL ( dStoresNew )

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

Tips: สังเกตว่า การสร้าง New Table ถือว่าได้ตารางดิบอันใหม่มาเลย จะใช้ ALL ปลด Filter ที่เป็นตัวสร้าง dStoresNew ไม่ได้นะครับ ดังนั้น StoreKey ก็ยังคงมีเฉพาะตั้งแต่เลข 100 อยู่ดี

กรณีอ้างอิงคอลัมน์ ไม่ว่าจะอันเดียวหรือหลายคอลัมน์

“จะปลด Filter ออก และได้ combination ที่ไม่ซ้ำกัน แต่มี Blank row พิเศษ“

ALL ( TableName[ColName1])

ใช้กับคอลัมน์จะได้ Blank Row พิเศษติดมาด้วยเช่นกัน

All_1Col = ALL(dStoresNew[ContinentName])

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

ALL ( TableName[ColName1], TableName[ColName2] , TableName[ColName3] )

และถ้าเราใช้ ALL อ้างอิงที่ระดับคอลัมน์ เราจะได้ Combination ของคอลัมน์นั้นๆ แบบไม่ซ้ำกันด้วย แต่ก็จะได้ Blank Row พิเศษติดมาด้วยเช่นกัน

AllMultiColumn = 
ALL(dStoresNew[StoreType],dStoresNew[ContinentName],dStoresNew[RegionCountryName])

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

ALLEXCEPT ก็คือเพื่อนของ ALL

สมมติว่าตาราง MiniTable มีคอลัมน์ชื่อ A,B,C,D,E,F แล้วเราอยากจะปลด FILTER และเลือก combination ทุกคอลัมน์ยกเว้น E กับ F เราสามารถเขียนได้ 2 แบบ คือ

=ALL(MiniTable[A],MiniTable[B],MiniTable[C],MiniTable[D])

หรือจะเขียนกลับด้านกันว่าปลดทุกอย่าง ยกเว้น E กับ F ก็ได้ และนั่นคือที่มาของ ALLEXCEPT

=ALLEXCEPT(MiniTable,MiniTable[E],MiniTable[F])

จุดเด่นที่สุดของ ALL คือการปลด Filter

สมมติว่าตารางที่ ALL อ้างอิงอยู่ มีการใส่ Filter เอาไว้ เช่น ใน Report

จะเห็นว่า ผมสร้าง Measure ตัวใหม่ขึ้นมาคือ AllRevenue โดยเขียนสูตรเหมือน TotalRevenue ทุกอย่างเลย ยกเว้นใส่คำว่า ALL(fSales) แทน fSales เฉยๆ

TotalRevenue = SUMX(fSales,fSales[SalesQuantity]*RELATED(dProduct[UnitPrice]))

AllRevenue = SUMX(ALL(fSales),fSales[SalesQuantity]* RELATED(dProduct[UnitPrice]))

ส่งผลให้ผลลัพธ์ที่ได้ ไม่ได้ถูก Filter จากตัวใดๆ เลย (ค่าเลยได้เท่ากับ Total แบบรวมทั้งหมด)

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

แม้ว่าเรา Filter ปี 2012 เข้าไปด้วย AllRevenue ก็ยังคงไม่เปลี่ยน แต่ TotalRevenue ถูก Filter ได้

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

หากอยากให้ปลด Filter ออกแค่ใน Visual นั้นๆ ให้ใช้ ALLSELECT แทน ALL ได้ครับ

AllSelectRevenue = SUMX(ALLSELECTED(fSales),fSales[SalesQuantity]*RELATED(dProduct[UnitPrice]))

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

พอคำนวณค่าเหล่านี้ได้ เราจะสร้าง Ratio คล้ายๆ ในบทความที่แล้วก็ได้ครับ แต่อันนี้จะได้เป็น %เทียบกับยอดรวม แต่จะมีความยืดหยุ่นกว่ากด % of Total ธรรมดาๆ เพราะเราสามารถปลด FILTER ออกแบบไหนก็ได้ตามที่ต้องการ ไม่ใช่แค่ปลดที่ตัวที่อยู่ใน Visual อย่างเดียวเหมือนเครื่องมือสำเร็จรูป

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

ตัวอย่างการปลด FILTER ออก แล้ว FILTER อีกตัวกลับเข้าไปใหม่

เช่น ถ้าเราอยากได้ยอด Revenue ของ Class Economy การ FILTER class=”economy” ลงไปเฉยๆ

FilterEconomyRevenue = SUMX(FILTER(fSales,RELATED(dProduct[ClassName])="economy"), fSales[SalesQuantity]*RELATED(dProduct[UnitPrice]))

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

ผลลัพธ์จะเท่ากับเป็นการ Filter เพิ่มลงไปแบบ AND ใน Filter Context เดิม ทำให้กรณีที่ Row Label มี Class เป็นตัวอื่น เช่น Deluxe จะหลายเป็น Deluxe และ Economy ซึ่งเป็นไปไม่ได้ จึงไม่เหลือผลลัพธ์จากการ Filter เลย

ดังนั้นถ้าอยากจะได้ Economy Revenue ทุกช่อง จะต้องมีการใช้ ALL มาช่วยปลด Filter ออกไปซะดังนี้

FilterEconomyRevenue = SUMX(FILTER(ALL(fSales),RELATED(dProduct[ClassName])="economy"), fSales[SalesQuantity]*RELATED(dProduct[UnitPrice]))

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

เราถึงจะได้ผลลัพธ์ที่ปลด Filter ตาราง fSales ออกให้หมดก่อน จากนั้นค่อย FILTER ให้มี Class เป็น economy อีกทีนึง

ลักษณะการปลด Filter ออกแล้วใส่ Filter ใหม่ลงไป เรียกได้อีกอย่างว่าเป็นการ “เปลี่ยน Filter Context” ซึ่งเป็นสิ่งที่ต้องทำบ่อยมากในการทำ Report Power BI เจ๋งๆ และมันคือหน้าที่หลักของสุดยอดฟังก์ชันที่ชื่อว่า CALCULATE ซึ่งจริงๆ ฝังความสามารถของ FILTER และ ALL อยู่ข้างในตัวมันเองอีกด้วย!!

ตอนต่อไปเจอกับ CALCULATE

CALCULATE ถือได้ว่าเป็นฟังก์ชันที่ลึกลับซับซ้อนและทรงพลังที่สุดอันนึงใน DAX เลยล่ะ ซึ่งเราจะได้เรียนเจ้า CALCULATE นี้ในบทถัดไปแล้วครับ ^^

สารบัญ Series Power BI

ใครสนใจอยากเรียนเป็นคลิปวีดีโอ ผมมี

March 26, 2020

Power BI ตอนที่ 11: เรียนรู้ DAX Table Function – FILTER
ในซีรีส์ Power BI บทก่อนหน้านี้เราได้เรียนรู้จักสูตร DAX พื้นฐานกันไปแล้ว คราวนี้เรามารู้จักฟังก์ชันกลุ่ม Table Function เพิ่มเติม นั่นก็คือ FILTER, DISTINCT, VALUES, ALL ซึ่งจะช่วยให้เราสามารถเขียนสูตร DAX ที่ซับซ้อนได้มากขึ้นในอนาคตนั่นเอง

แต่เพื่อไม่ให้มันเยอะเกินไป เดี๋ยวบทความนี้ผมจะพูดถึง FILTER แค่ตัวเดียวก่อน ส่วน ALL, DISTINCT, VALUES และผองเพื่อน จะพูดในตอนถัดไปนะครับ

ไฟล์ประกอบ

ใช้ไฟล์เดิมที่ทำมาจากตอนก่อนหน้าได้เลยนะครับ หรือจะใช้อันนี้ก็ได้

Table Function คืออะไร?

คำว่า Table Function ก็คือ ฟังก์ชันที่ให้ผลลัพธ์กลับมาเป็น Table หรือตารางนั่นเอง ต้องบอกว่าฟังก์ชันปกติที่เราคุ้นเคยกันก่อนหน้านี้ทุกอัน จะให้คำตอบออกมาเป็นค่าเดียว (เรียกว่า Scalar Value) ไม่ว่าจะเป็นประเภทตัวเลข ตัวหนังสือ logic ก็ตาม ก็จะมีค่าเดียวมาโดยตลอด

แต่เจ้า Table Function ไม่ใช่แบบนั้น มันดันให้ผลลัพธ์กลับมาเป็นตารางเลย เรามาทำความรู้จักมันทีละตัวกันครับ

FILTER

ซึ่งตัวที่น่าจะเห็นหน้าตาเป็นตารางได้ชัดเจนที่สุดก็คือ เจ้า FILTER นี่แหละ เพราะมันจะให้ผลลัพธ์เป็นตารางที่ผ่านการคัดกรองจนเหลือสิ่งที่ต้องการแล้วนั่นเอง (คล้ายๆ กับ FILTER ซึ่งเป็นฟังก์ชันของ Excel 365 เลยเนอะ แต่มันเป็นคนละตัวกันนะครับ อันที่สอนในบทความนี้คือใน DAX)

วิธีการใช้งานคือ
```
FILTER ( <Table>, <FilterExpression> )
```
โดยที่ <Table> ก็คือตาราง Original ซึ่งจะเป็นแค่การใส่ชื่อตารางธรรมดาๆ หรือ เป็น Table Function ตัวอื่นก็ยังได้

ส่วน <FilterExpression> ก็คือเงื่อนไขในการคัดกรอง โดยมันจะต้องเขียนสูตรส่วนนี้ให้ได้ผลลัพธ์เป็น TRUE/FALSE เพื่อคัดกรองเอาเฉพาะตัวที่เป็น TRUE มาแสดง

สมมติว่า ผมอยากจะ Filter ตาราง dProduct ด้วยสูตร ผมสามารถใช้ฟังก์ชัน FILTER นี้ได้เลย และเพื่อให้เห็นผลลัพธ์ชัดเจนที่สุด ผมจะใช้คำสั่ง New Table เพื่อให้มันออกมาเป็นตารางอีกอันนึงชัดๆ เลย

ผมใส่สูตรว่า
```
myTable = FILTER(dProduct,dProduct[UnitPrice]>200)
```
แปลว่า ผมเอาตาราง dProduct มา Filter นะ โดยเงื่อนไขคือ dProduct[UnitPrice]>200 ซึ่งแปลว่า คอลัมน์ UnitPrice มีค่ามากกว่า 200 นั่นเอง

ซึ่งผลลัพธ์ก็จะออกมาเป็น Table ที่มีจำนวนคอลัมน์เท่ากับ dProduct ทุกประการ แค่มีจำนวนแถวลดลง เพราะมีการคัดกรองตามเงื่อนไขใน <FilterExpression>

ปล. Power BI มีอัปเดทหน้าตา UI ใหม่นิดหน่อยนะ (ให้เหมือน Ribbon ใน Excel มากขึ้น) หน้าตาเลยแปลกไปจากบทความก่อน

การอ้างอิงคอลัมน์ที่จะ FILTER

<FilterExpression> ของ FILTER เนี่ยปกติจะสามารถอ้างอิงคอลัมน์ได้เฉพาะจาก Field ที่อยู่ใน <Table> ที่อ้างถึงเท่านั้น แต่ถ้าอยากจะอ้างอิงข้ามตารางต้องใช้ RELATED มาช่วยนะครับ

ตัวอย่างการใช้ RELATED ใน FILTER

หากเราต้องการ Filter fSales ให้เหลือรายการเฉพาะที่มาจากช่องทาง online เท่านั้น แม้ว่าจะไม่มี StoreType ให้เลือกในตารางนี้ตรงๆ เราก็ใช้ RELATED ดึงมามาช่วยได้ครับ
```
OnlinefSales = FILTER(fSales,RELATED(dStores[StoreType])="online")
```
แล้วถ้าจะใส่เงื่อนไขมากกว่า 1 อย่างล่ะ?

ถ้าสังเกตดูจะเห็นว่า input ที่เป็นเงื่อนไขของ FILTER นั้นมีแค่ตัวเดียว แปลว่าเราไม่สามารถคั่นด้วยเครื่องหมาย , ได้แบบ SUMIFS นะ แปลว่าเราจะต้องใช้เงื่อนไขแบบ AND OR มาช่วยแทนนั่นเอง

เงื่อนไขแบบ AND

สามารถใช้ฟังก์ชัน AND(เงื่อนไข1,เงื่อนไข2) ได้ แต่ก็จะได้แค่ 2 เงื่อนไขนี่แหละ
```
myTable = FILTER(dProduct,
AND(dProduct[UnitPrice]>200,dProduct[BrandName]="Litware"))
```
แปลว่าเอาที่ Unit Price > 200 และ ฺBrandName เป็น Litware

ถ้าจะใช้มากกว่านี้ให้ใช้เครื่องหมาย && มาช่วยแทน
เช่น เงื่อนไข1 && เงื่อนไข2 && เงื่อนไข3
```
myTable = FILTER(dProduct,
dProduct[UnitPrice]>200 && dProduct[BrandName]="Litware" && dProduct[ClassName]="Deluxe")
```
แปลว่าเอาที่ Unit Price > 200 และ ฺBrandName เป็น Litware และ ClassName เป็น Deluxe

Tips : ถ้าอยากจะเขียน FILTER ซ้อนไปอีกชั้น เพื่อทำเงื่อนไขซ้อนกันแบบ AND ก็ย่อมได้นะ เพราะซ้อนไปเรื่อยๆ ก็แปลว่าต้องผ่านทุกเงื่อนไขนั่นแหละ จึงเป็น AND โดยปริยาย แต่ผมว่ามันอ่านยากเปล่าๆ อย่าทำเลย

เงื่อนไขแบบ OR

สามารถใช้ฟังก์ชัน OR(เงื่อนไข1,เงื่อนไข2) ได้ แต่ก็จะได้แค่ 2 เงื่อนไขเช่นกัน
```
myTable = FILTER(dProduct,
OR(dProduct[UnitPrice]>1000, dProduct[BrandName]="Litware"))
```
แปลว่าเอาที่ Unit Price > 1000 หรือ BrandName เป็น Litware

ถ้าจะใช้มากกว่านี้ให้ใช้เครื่อบหมาย || มาช่วยแทน
เช่น เงื่อนไข1 || เงื่อนไข2 || เงื่อนไข3
```
myTable = FILTER(dProduct,dProduct[UnitPrice]>1000 || dProduct[BrandName]="Litware" || dProduct[BrandName]="Proseware")
```
แปลว่าเอาที่ Unit Price > 1000 หรือ BrandName เป็น Litware หรือ Proseware

แต่ถ้ากรณีจะอ้างอิงค่าใน Field เดียวกันหลายๆ item อย่าง Litware กับ Proseware เราไม่ต้องใช้ OR ก็ได้ แต่หันมาใช้การอ้างอิงเงื่อนไขแบบ field IN {item1, item2} แทนจะง่ายกว่าการเขียนว่า field = item1 ||field = item2 เช่น
```
myTable = FILTER(dProduct,dProduct[UnitPrice]>1000 || dProduct[BrandName] IN {"Litware" , "Proseware"} )
```
จะได้ผลลัพธ์เหมือนกันทุกประการเลยครับ

ตัวอย่างทั้งหมดที่ผ่านมายังเป็นการใช้งานพื้นฐานแบบให้เห็นภาพชัดๆ คือลองสร้าง New Table ขึ้นมาเลย แต่ในความเป็นจริงเราสามารถเอา FILTER ไปประกอบกับสูตรอื่นๆ ที่ต้องการ input เป็น Table ได้อีกนะครับ เช่น สร้าง Measure ยอดขายเฉพาะยอดแบบ Online เท่านั้น

ตัวอย่างการใช้ FILTER ใน Measure

ผมลองสร้าง New Measure ตัวใหม่ ที่สูตรเหมือน TotalRevenue ทุกอย่าง แค่ส่วนของ Table ใน SUMX เราใช้ FILTER สร้างตารางจำลองขึ้นมาให้เหลือเฉพาะ Transaction ที่มาจากช่องทาง Online เท่านั้น เพื่อที่ผลลัพธ์มันจะได้เอาจำนวน SalesQuantity * UnitPrice เฉพาะ Row ที่มาจากช่องทางออนไลน์ไง
```
TotalRevenue = SUMX(fSales,fSales[SalesQuantity]*RELATED(dProduct[UnitPrice]))
```
```
TotalOnlineRevenue = SUMX(FILTER(fSales,RELATED(dStores[StoreType])="Online"),
fSales[SalesQuantity]*RELATED(dProduct[UnitPrice]))
```
พอเรามี Measure 2 ตัวแล้ว เราก็สามารถสร้าง %Portion ที่น่าสนใจได้ เช่น

และนี่ก็เป็นตัวอย่างการใช้ FILTER ครับ เดี๋ยวบทความหน้าเรามาต่อกันที่ ALL, DISTINCT, VALUES และผองเพื่อนกันครับ รับรองว่าลึกซึ้งขึ้นไปอีกครับ

ทั้งหมดนี้ เพื่อปูทางไปสู่ฟังก์ชันที่ลึกซึ้งที่สุดอันนึงใน DAX นั่นก็คือ CALCULATE ครับ!!

สารบัญ Series Power BI
ใครสนใจอยากเรียนเป็นคลิปวีดีโอ ผมมี
March 25, 2020
เปลี่ยนสูตรยากให้เป็นสูตรกล้วยๆ ด้วย Dynamic Array ใน Excel 365
ผมเชื่อว่าหลายคนที่ใช้ Excel นั้นย่อมเคยต้องการให้ Excel ทำงานจัดการข้อมูลในลักษณะนี้ เช่น
- คัดกรองข้อมูลให้เหลือสิ่งที่ต้องการเท่านั้น (Filter)
- จัดเรียงข้องมูลใหม่ตามต้องการ (Sort)
- ตัดข้อมูลซ้ำออกให้เหลือเฉพาะที่ไม่ซ้ำ (Remove Duplicates)
ตั้งแต่อดีตที่ผ่านมา จริงๆ Excel ก็มีเครื่องมือที่พร้อมที่จะทำงานเรื่องเหล่านี้อยู่แล้ว ใน Ribbon เครื่องมือ Data เช่น Filter, Sort, Remove Duplicates และผมก็เชื่อว่ามันเป็นเครื่องมือที่หลายๆ คนน่าจะพอใช้กันเป็นอยู่แล้วล่ะ

อย่างไรก็ตามเครื่องมือเหล่านั้นแม้จะใช้ง่าย แต่ก็มีจุดอ่อนสำคัญคือ มันเป็นเครื่องมือที่ต้องให้เรากดปุ่มสั่งใหม่ทุกครั้ง เช่น จะ Filter แบบไหน Sort ยังไง รวมถึง Remove Duplicates ข้อมูลชุดไหน แปลว่ามันยังต้องมี Step ที่ต้องการมนุษย์มา “ทำงาน Manual” บางอย่างอยู่

ใน Excel ยุคใหม่ขึ้นมาหน่อย ก็ได้มีเครื่องมือ Power Query เข้ามาช่วยแก้ปัญหานี้ เพราะมันก็ Filter ข้อมูลได้ Sort ได้ Remove Duplicates ได้ (และทำอย่างอื่นได้อีกเยอะแยะ) และที่สำคัญคือ มันกด Refresh เพื่อทำซ้ำ Step ทุกอย่างที่กำหนดไว้ได้! อย่างไรก็ตาม มันก็ยังต้องมี Step การกด Refresh อีกทีนึงอยู่ดี (ยกเว้นใช้ VBA ช่วย)

ดังนั้นถ้าหากเราต้องการความอัตโนมัติขั้นสุดยอด วิธีที่ทำได้ก็คงหนีไม่พ้น “การเขียนสูตร” ซึ่งบอกเลยว่าการเขียนสูตรเพื่อ Filter/Sort/Remove Duplicates นั้นเคยเป็นเรื่องที่ยากมากกกกกกกกกกๆๆๆๆๆ มาโดยตลอด (คิดดูว่าถ้าจะ Filter ข้อมูลจริงๆ มันก็คล้ายๆ จะต้อง VLOOKUP หรือ INDEX+MATCH นั่นแหละ และปกติสูตรพวกนี้ก็จะเจอแต่ตัวแรกอีก ยากจะตายกว่าจะเอามาครบทุกตัวได้)

จนกระทั่งมีสูตรชุดใหม่เกิดขึ้นมา เป็นสูตรในกลุ่ม Dynamic Array ซึ่งเป็นความสามารถใหม่ของ Excel 365 ที่จริงๆ มีหลายตัว แต่ผมจะขอแนะนำให้รู้จักแค่ 3 ตัวก่อน นั่นคือ FILTER, SORT และ UNIQUE ครับ

** ใครใช้ Excel 365 แล้วยังไม่มี ฟังก์ชัน FILTER, SORT และ UNIQUE ให้ใช้ ลองเช็คดูนะครับว่าอัปเดทเป็น version ล่าสุดรึยัง? วิธีอัปเดทดูได้ที่นี่

เรามาดูข้อมูลตัวอย่างกัน

โหลดข้อมูลตัวอย่างได้ที่นี่

มาดูการทำงานทีละฟังก์ชันก่อนนะ

ก่อนอื่นมาดูตัวที่เข้าใจง่ายก่อน นั่นคือ UNIQUE

UNIQUE เอาไว้คัดมาเฉพาะตัวที่ไม่ซ้ำกัน

วิธีใช้งานแบบง่ายที่สุดคือ
```
=UNIQUE(rangeข้อมูล)
```
ซึ่งง่ายมากๆๆๆๆ

ถ้าไม่มีฟังก์ชัน UNIQUE เราต้องเขียนสูตรผสมฟังก์ชันต่างๆ ออกมายาวเป็นกิโล ที่ทั้งยาวและยาก คนทั่วไปอ่านไม่เข้าใจแน่นอน เช่น ในรูปนี้
```
=IFERROR(INDEX($B$2:$B$15,MATCH(0,COUNTIFS($I$2:I2,$B$2:$B$15),0)),"")
```
แต่พอมี UNIQUE แล้ว เหลือแค่นี้ =UNIQUE(B2:B15) !!

ผลลัพธ์จะได้ข้อมูลแบบไม่ซ้ำกันเลย แบบนี้ และผลลัพธ์จะงอกออกมาเท่ากับจำนวนข้อมูลด้วย

ถ้ามีสินค้าเพิ่มขึ้นในข้อมูลดิบ เช่น ผมเพิ่มเจลล้างมือลงไป ผลลัพธ์ของ UNIQUE ก็จะเพิ่มมาทันที!!

Dynamic Array กับการงอกผลลัพธ์แบบ Spill

การที่ผลลัพธ์งอกอัตโนมัติแบบนี้ มีศัพท์เทคนิคเรียกว่า Spill นะครับ ซึ่งเป็น Concept ใหม่ของ Array Formula ใน Excel 365 ที่เรียกว่า “Dynamic Array” โดยที่สูตรจะอยู่ช่องซ้ายบนของพื้นที่ที่ Spill เท่านั้น (คล้ายๆ Merge Cell) ถ้าคลิ๊กที่ช่องอื่นสูตรจะเป็นสีเทา
```
=UNIQUE(E2:E15)
```
โดยถ้าพื้นที่ที่จะงอกมีข้อมูลอื่นขวางอยู่มันจะขึ้น Spill Error ว่า #SPILL! แบบนี้

กลับมาดูเรื่อง UNIQUE ต่อกัน

การใช้งานแบบเต็มความสามารถของ UNIQUE คือ
```
=UNIQUE(array, [by_col] , [exactly_once] )
```
- array คือ range ข้อมูลต้นฉบับ
- [by_col] ถ้าเป็น TRUE คือจะคิด Unique by column คือจะกำจัดตัวซ้ำที่อยู่ในแถวเดียวกันออก (ซึ่งปกติค่านี้จะเป็น FALSE คือ ให้เอาที่ซ้ำกันในคอลัมน์เดียวกันออก) ซึ่งผมว่าแทบไม่ค่อยได้ใช้หรอก
- ส่วน [exactly_once] ถ้าใส่เป็น TRUE คือจะคัดให้เหลือเฉพาะข้อมูลที่โผล่มาครั้งเดียวใน Data Source เท่านั้น อันนี้เดี๋ยวลองกับวันที่ให้ดู (ปกติอันนี้จะเป็น FALSE)
```
=UNIQUE(B2:B15,FALSE,TRUE)
```
ผลลัพธ์ออกมาเป็น 4 หมื่นกว่าๆ เพราะยังไม่ได้ปรับ Number Format ดังนั้นให้ลากครอบแล้วปรับ Number Format ให้เป็น Date ซะ (เลือกพื้นที่เผื่อเอาลงมาเยอะๆก็ได้)

สังเกตว่าไม่มีวันที่ 1/2/2020 และ 7/2/2020 เพราะมันไม่ได้มีแค่ตัวเดียว

SORT เอาไว้เรียงข้อมูลตามที่ต้องการ

แบบง่ายสุดก็คือ
```
=SORT(range ข้อมูล)
```
เช่น

แต่การใช้งานแบบเต็มความสามารถ คือ
```
=SORT(array, [sort_index], [sort_order], [by_col])
```
- array คือ range ข้อมูลต้นฉบับ
- [sort_index] คือ จะเรียงตามคอลัมน์ที่เท่าไหร่ (ปกติเรียงตามคอลัมน์แรก)
- [sort_order] ถ้าเป็น 1 คือ น้อยไปมาก ส่วน -1 คือ มากไปน้อย (ปกติจะเป็น 1)
- [by_col] คือ ถ้าเป็น TRUE คือจะเรียงตามแนวนอน (ปกติเป็น FALSE) อันนี้ไม่ค่อยได้ใช้อีกนั่นแหละ
ตัวอย่างเช่น

ถ้าผม Sort ทั้งพื้นที่เหลือง แต่ให้เรียงตามคอลัมน์ที่ 3 ของพื้นที่เหลือง (ก็คือ ลูกค้า) ก็จะได้ดังนี้
```
=SORT(A2:D15,3,1)
```
FILTER เอาไว้คัดเลือกให้เหลือเฉพาะสิ่งที่ต้องการ
```
=FILTER(array,include,[if_empty])
```
- array คือ range ข้อมูลต้นฉบับที่จะนำมา Filter
- include คือ range ของเงื่อนไขโดยจะเอาเฉพาะค่าที่เป็น TRUE มาแสดง (สามารถใส่ condition ใน Range ที่ไม่อยู่ใน array ได้)
- [if_empty] คือ ถ้าไม่มีผลลัพธ์เหลือเลยจะให้ขึ้นว่าอะไร
ตัวอย่างเช่น
```
=FILTER(C2:E15,D2:D15="sales ค","ไม่มีข้อมูล")
```
array คือพื้นที่สีเหลือง

include เขียนว่า D2:D15=”sales ค” ซึ่งจริงๆ ถ้า Highlight แล้วกด F9 ดูจะได้เป็นค่า TRUE/FALSE ดังนี้

เจ้าฟังก์ชัน FILTER นี่จะเอาเฉพาะรายการที่เป็น TRUE มาเท่านั้น ก็เลยได้แค่ item อันที่ 2 กับ 11 นั่นเอง

ถ้าจะ Filter หลายเงื่อนไข เราต้องรู้จักว่า การเอาเงื่อนไขคูณกัน จะเป็นเงื่อนไขเป็น AND (TRUE * TRUE ได้ 1 นอกนั้นเป็น 0)

เช่น ถ้าอยากได้เฉพาะรายการของ sales ก และ อาหาร เท่านั้น ก็เขียนได้ว่า
```
=FILTER(C2:E15,(D2:D15="sales ก")*(E2:E15="อาหาร"))
```
ใช้ฟังก์ชันผสมผสานกัน

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

ตัวอย่างเช่น ถ้าผมใช้ UNIQUE กับ SORT ซ้อนกันจะได้แบบนี้

การเขียนฟังก์ชันซ้อนกันในสูตรเดียว

การเขียน
```
=SORT(UNIQUE(D2:D15))
```
เป็นการเขียนฟังก์ชันซ้อนกันในสูตรเดียว เหมาะกับคนที่เขียนสูตรคล่องๆ แล้ว

การเขียนฟังก์ชันแยก Cell กัน แต่อ้างอิงข้อมูลที่ Spill

อย่างไรก็ตามถ้าเราต้องการเขียน UNIQUE ก่อน แล้วค่อย SORT แยกออกมาต่างหาก ก็สามารถทำได้ โดยการอ้างอิงไปที่ช่องซ้ายบนของพื้นที่ Spill แล้วตามด้วยเครื่องหมาย# เพื่อให้ Excel เข้าใจว่าเป็นการอ้างอิงตัวที่ Spill ทั้งชุด ไม่ได้อ้างอิงแค่ Cell เดียว เช่น

เดี๋ยวลองผสมกัน 3 ตัวเลย

ใส่ Filter เลือก sales ก ก่อน
```
ที่ K8 =FILTER(E2:F15,D2:D15=K3)
```
จากนั้นใส่ Sort ให้เรียงจากน้อยไปมาก ตามชื่อสินค้า
```
ที่ K8 =SORT(FILTER(E2:F15,D2:D15=K3))
```
Tips : ถ้ารู้สึกยังไม่ถูกใจ เพราะราคายังไม่เรียงด้วย แปลว่าต้อง Sort หลาย Step ซึ่งจริงๆใช้ฟังก์ชัน SORTBY จะง่ายกว่า แต่ผมยังไม่ได้สอน 555 ไม่เป็นไร ใช้ SORT ก็ทำได้แต่ต้องใส่ซ้อนกัน เช่น SORT index 2 ก่อน แล้วค่อย Sort index 1 ครอบทีหลังดังนี้
```
ที่ K8 =SORT(SORT(FILTER(E2:F15,D2:D15=K3),2),1)
```
จากนั้นใส่ UNIQUE เพื่อกำจัดตัวซ้ำ
```
ที่ K8 =UNIQUE(SORT(SORT(FILTER(E2:F15,D2:D15=K3),2),1))
```
ลองเปลี่ยน sales ก เป็นคนอื่นบ้าง เช่น sales ค

ตัวอย่างการพลิกแพลงสูตร

ซึ่งหากเรามีความรู้เรื่องฟังก์ชันอื่นๆ ด้วย เราก็สามารถทำอะไรที่พลิกแพลงได้กว่านี้มาก

ตัวอย่างการสร้าง Dropdown List ที่มี Item เพิ่มตามข้อมูลที่เปลี่ยนไป

ให้เราเขียน Dynamic Array ทิ้งไว้ แล้วค่อยอ้างอิงเข้าไปใน Data Validation

หากอยากให้สามารถเห็นข้อมูลใหม่ที่อาจเพิ่มในอนาคตได้

ก็ทำให้ตารางเป็น Table ซะสิ เพราะความสามารถที่เด่นที่สุดของ Table คือการขยายอาณาเขตตัวเองได้เวลามีข้อมูลเพิ่มด้านขวาหรือด้านล่าง

ตัวอย่าง เลียนแบบ Pivot Table แบบมีทั้ง Row และ Column

เช่นสามารถได้ตารางที่ใกล้เคียง Pivot Table เลย โดยในนี้เพิ่มฟังก์ชัน TRANSPOSE ที่เอาไว้พลิกสลับแนวนอนเป็นแนวตั้ง และมีการใช้ SUMIFS มาช่วย โดยระบุ Criteria แบบ Array เป็นตัวที่ Spill
```
ที่ M5 : =SORT(UNIQUE(C2:C15))
ที่ N4 : =TRANSPOSE(SORT(UNIQUE(E2:E15)))
ที่ N5 : =SUMIFS(H2:H15,C2:C15,M5#,E2:E15,N4#,D2:D15,M1)
```
ตัวอย่าง เลียนแบบ Pivot Table แบบมี row 2 ชั้น

โดยเลือกมาบางคอลัมน์ด้วย CHOOSE ผสม Array Constant และหาผลสรุปด้วย SUMIFS ผสมกับเลือก Criteria บางส่วนของที่ Spill ด้วย INDEX
```
ที่ K5 : =SORT(FILTER(CHOOSE({1,2},C2:C15,E2:E15,H2:H15),D2:D15=K1))
ที่ M5 : =SUMIFS(H2:H15,C2:C15,INDEX(K5#,0,1),E2:E15,INDEX(K5#,0,2),D2:D15,K1)
```
สรุป

เป็นอย่างไรบ้างกับฟังก์ชันใหม่ของ Excel 365 ? ถึงมันจะดูยากไปบ้างเมื่อเทียบกับการใช้ Pivot Table แต่ว่าสิ่งที่ได้กลับมาคือความอัตโนมัติแบบสุดๆ นั่นเอง

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

ดังนั้นลองเลือกดว่าจะลองหัดใช้สูตรใหม่นี้หรือไม่ และถ้าจะใช้ ประเด็นที่ต้องพิจารณาคือ หากสิ่งไฟล์ไปให้คนอื่นที่มี Excel Version เก่า ก็จะเปิดไฟล์มาแล้ว Error นะ ดังนั้นระวังให้ดีด้วยนะครับ *o*
March 23, 2020
เลิกใช้ Merge Cell กันเถอะ (แล้วจะให้ทำยังไงแทน?)
“มาเลิกใช้ Merge Cell กันเถอะ!” นี่คือความในใจที่อยากจะตะโกนบอกคนใช้ Excel ทุกคนเลย 555

แต่ผมไม่ได้มาห้ามเฉยๆ นะ เพราะในบทความผมมีวิธีหลีกเลี่ยงที่พอจะช่วยลดปัญหาของ Merge Cell มาบอกด้วยล่ะ

หากผมจะบอกว่า “การ Merge Cell คือต้นกำเนิดของความเลวร้ายทั้งปวงใน Excel” มันอาจจะดูแรงเกินไปนิด แต่มันก็ใกล้เคียงล่ะ มาดูกันว่าทำไม…

ปัญหาของการ Merge Cell

หากเรามีการใช้ Merge Cell ที่ตัวข้อมูลดิบที่เป็น Input จะพบว่ามันก่อให้เกิดปัญหามากมาย
- Merge แบบหัวตาราง 2 ชั้น (กรอบชมพูในรูปบน)
  - ทำให้เลือก Sort/Filter หัวตารางลำบาก ต้องเลือกทั้งแถวที่สองแล้วกด Filter เอาถึงจะเลือกหัวตารางชั้นล่างได้ ซึ่งยุ่งยากมาก
  - เอาข้อมูลเข้าไปวิเคราะห์ใน Pivot Table ก็ไม่ได้อีก (เพราะถือว่ามีชื่อ Field ว่าง)
- การ Merge ที่ Data ข้างใน (กรอบแดงในรูปบน) จะทำให้
  - ไม่สามารถ Sort/Filter ข้อมูลที่มีการ Merge Cell ได้
- Copy Paste ข้อมูลลงบน Merge Cell โดยที่ขนาดไม่เท่าเดิมก็ไม่ได้
- แถมยังเป็นการเว้นช่องว่างเอาไว้ในฐานที่มนุษย์เข้าใจแต่คอมพิวเตอร์ไม่เข้าใจ (Computer เข้าใจว่าข้อมูลอยู่ที่ช่องซ้ายบนเท่านั้น นอกนั้นเป็นช่องว่าง)
- การเขียนสูตรเพื่อเลือกข้อมูลช่องที่ถูก Merge แต่ไม่ใช่ช่องซ้ายบนก็ยากอีก
ทำไมคนยังใช้ Merge Cell?

จะเห็นว่าปัญหามากมายนั้นเกิดจากการใช้ Merge Cells แต่ทำไมคนถึงใช้มันอีกล่ะ??

คำตอบที่ผมพบจากการสอบถามเพื่อนๆ ในเพจ สามารถสรุปเหตุผลได้สั้นๆ ก็คือ เป็น “เพราะมันสวยดี”

ผมขอบอกเลยว่า ความงามที่ทำให้เกิดปัญหาตามมานั้น ย่อมไม่ใช่ความงามที่แท้ ดังนั้นผมขอแนะนำให้หลีกเลี่ยงการ Merge Cell ถ้าไม่จำเป็นจริงๆ

ผมเชื่อว่า คนที่ใช้ Merge Cell ส่วนใหญ่ไม่รู้ด้วยว่ามันจะส่งผลให้มีปัญหาในอนาคตแค่ไหน เพราะอาจจะไม่ค่อยเข้าใจการใช้ Sort/Filter/Table/Pivot Table

ทำไม Excel ถึงไม่ยอมให้เรา Sort/Filter/Pivot ข้อมูลที่ Merge?

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

เวลาพูดว่าอย่า Merge Cell ที่ห้ามจริงๆ หมายถึงตัว Data Input ไม่ใช่ Report Output

หลักการที่ถูกต้องคือ แยกข้อมูลส่วน Input กับ Output ออกจากกันซะ ส่วน Input ที่เป็นข้อมูลดิบ ให้ทำให้อยู่ในลักษณะ Database ให้มากที่สุด (แต่ถ้ากรอกไม่ไหวจริงๆ ก็ใช้ Power Query ช่วย Fill Down/Unpivot แปลงหน้าตาได้ ถ้าทำเป็น) ส่วน Output ถ้าอยากจะ Merge ก็แล้วแต่ ไม่มีใครว่า (ยกเว้นวันจะต้องไปเป็น input ของชาวบ้านอีกที)

แปลว่า ถ้าเราจะ Merge เพื่อความสวยงาม ให้ทำที่ Output ซึ่งก็คือตัว Report ไม่ใช่ Input ที่เป็นแหล่งข้อมูลดิบ

วิธีที่พอจะช่วยหลีกเลี่ยง Merge Cell (ถ้ายังอยากทำ)

กรณีจะ Merge แนวนอน ให้ใช้ Center Across Selection แทน

เทคนิคนี้เป็นตัวที่สามารถสร้างผลลัพธ์ที่หน้าตาจะเหมือนกับ Merge Cell มากๆ (แต่ใช้รวม cell ได้แต่แนวนอน ใช้รวมแนวตั้งไม่ได้) มันคือการจัด Format ให้กับข้อมูลในแนวนอน โดยให้ข้อความมาอยู่ตรงกลางของพื้นที่ที่เลือกได้

ดังนั้นหากคุณต้องการจะ Merge Cell แค่แถวเดียว ให้เปลี่ยนมาใช้ Center Across Selection จะดีกว่ากันมากๆ เลย

วิธีทำก็แค่เลือกข้อมูล แบบที่ยังไม่ต้องMerge

จากนั้นก็คลิ๊กขวา Format Cell หรือกด Ctrl+1 ก็ได้

จากนั้นเลือก Alignment -> Horizontal : Center Across Selection

ผลที่ได้จะเหมือนกับการ Merge Cell เลย แต่ Cell ไม่ได้ Merge (เห็นมะว่ายังคลิ๊กเลือก cell แยกกันได้) ส่วนข้อมูลจริงจะยังคงอยู่ที่ช่องซ้ายสุดของพื้นที่ช่องเดียวเหมือนเดิม

ดังนั้นสรุปว่าถ้าจะ Merge Cell แนวนอนจริงๆ ก็ให้ใช้ Center Across Selection แทนนะ แต่ถ้าเลือกได้จริงๆ ก็อย่าไปทำมันเลยทั้งคู่นั่นแหละ ให้พิมพ์ข้อมูลให้ครบทุกช่องดีกว่า เพราะอย่างที่บอกไปว่าการ Merge ข้อมูลมันไม่ได้อยู่ครบทุกช่อง ไอ้เจ้า Center Across Selection ก็มีข้อมูลอยู่แต่ช่องซ้ายสุดเหมือนกัน เดี๋ยวก็มีปัญหาอีก

กรณีจะ Merge แนวตั้ง ให้พิมพ์ให้ครบทุกช่องเถอะ ไม่มีทางเลือก

การ Merge แนวตั้ง ใช้ Center Across Selection ไม่ได้นะ และไม่น่าจะมีวิธีที่ดีไปกว่านี้แล้วล่ะ เสียใจด้วย

กรณี Merge หัวตาราง พอมีทางช่วยได้

กรณีที่เรามีหัวตาราง 2 ชั้น มันทำให้ใช้ PivotTable ไม่ได้โดยสิ้นเชิง ดังนั้นวิธีแก้ที่ดีที่สุดคือ ทำหัวตารางให้มีแค่ชั้นเดียวพอ

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

และอย่าลืมทำลายข้อมูลที่ Merge อยู่ให้หมด (วิธีกำจัด Merge Cell ที่ง่ายที่สุดคือเลือกคลุมทั้งหมด แล้วกดปุ่ม Merge Cell อีกรอบ มันจะปลดออกหมดเลย) แล้วกรอกให้ครบทุกช่องซะ อย่าปล่อยว่างเลยและตัวสรุปรวมท้ายตารางให้ลบทิ้งไปก่อนทั้งบรรทัดเลย

จากนั้น Copy Paste Value ซะแล้วแก้บางคอลัมน์ที่อ่านไม่รู้เรื่องให้ถูกต้อง (ตัวที่ Merge ทั้งหลาย)

จากนั้นพอจะใช้งานจริง แล้วค่อยเลือกที่ช่อง C3 แล้วสั่ง Freeze Pane ซะแบบนี้

พอ Scroll Mouse ลงมา ไอ้ส่วนหัวตารางสองชั้นสวยๆ ของเราก็จะยังอยู่

แต่วิธีนี้เราจะได้ความสามารถของการ Sort/Filter กลับคือนมา และยังสามารถทำข้อมูลเป็น Table และใช้ PivotTable ได้ด้วย ทั้งนี้เพราะเราสร้างหัวตาราง 1 บรรทัดให้อยู่ติดกับตัวข้อมูลไว้แล้วนั่นเอง

เอายังไงกับตัวสรุปรวมท้ายตาราง

หากเราต้องการสรุปข้อมูลจริงๆ ควรจะใช้ Pivot Table ทำมากกว่าการมาเขียนสูตรเอาท้ายตาราง แต่ถ้าต้องการให้มีการสรุปข้อมูลอยู่ที่ท้ายตารางจริงๆ ผมแนะนำให้ใช้ความสามารถของ Table ครับ

ก่อนอื่นให้เลือกข้อมูลช่องใดช่องหนึ่งแล้วกด Insert->Table หรือ Ctrl+T เพื่อแปลงข้อมูลเป็น Table ซะ ซึ่งตอนแรกมันจะยังไม่มีตัวสรุปท้ายตารางให้

ถ้าอยากให้มีสรุปท้ายตาราง แค่ติ๊กเลือก Total Row ตามรูป ก็จะมีบรรทัดสรุปโผล่มา

จากนั้นเราสามารถเลือกแต่ละคอลัมน์ได้ว่าจะให้สรุปด้วยวิธีไหน เช่น SUM COUNT AVERAGE MAX MIN

เราได้ตารางที่หน้าตาไม่ได้แย่เลย มีหัวตาราง 2 ชั้นเหมือนเดิม มีบรรทัดสรุป แค่เนื้อในต้องพิมพ์ให้ครบทุกช่องเท่านั้นเอง เพื่อที่จะได้ Sort/Filter/Pivot ได้ตามปกติครับ

และทั้งหมดนี้ก็คือเทคนิค ที่พอจะช่วยหลีกเลี่ยงการทำ Merge Cell ได้นะครับ ถ้าทำตามนี้รับรองว่าชีวิตดีขึ้นแน่นอน ^^
March 21, 2020
ฐานข้อมูลตำบล อำเภอ จังหวัด รหัสไปรษณีย์ ของประเทศไทย V3: ข้อมูลสมบูรณ์มากขึ้น
สืบเนื่องจากที่ผมได้พยายามทำ “ฐานข้อมูลส่วนกลาง” หรือที่ผมจะเรียกว่า “Common Database” ที่หลายๆ คนก็น่าจะอยากใช้เหมือนกัน เช่น ฐานข้อมูลตำบล อำเภอ จังหวัด ภาค ทั้งภาษาไทยและภาษาอังกฤษ เพื่อที่เพื่อนๆ จะได้ไม่ต้องมาเสียเวลาทำเหมือนผมอีก

ตอนนี้ผมทำเสร็จแล้วในระดับนึง คือ ได้ ภาค จังหวัด อำเภอ ตำบล แบบที่เรียกได้ว่าครบถ้วนที่สุดอันนึงในโลก internet แล้วล่ะ 555 (ถ้าไม่นับ Wikipedia นะ ) ก็เลยจะเอามาแจกเพื่อนๆ ให้ลองเอาไปใช้ดูก่อน ( ตอนนี้เพิ่งเพิ่มรหัสไปรษณีย์ไปให้แล้วนะ เมื่อ 17 Mar 2020)

ที่บอกว่าครบถ้วน เพราะผมมีการ crosscheck หลาย source มากๆ ถ้าอันไหนไม่ตรงกันจะเปิด wiki ดูทีละเขต ทีละอำเภอเลย (รายละเอียดของ source อยู่ในไฟล์นะ) ที่ต้อง cross check เพราะไม่มีแหล่งใดถูกต้องสมบูรณ์เลย ขนาดว่าของราชบัณฑิตยังมีชื่อไทยผิดพลาดหลายจุด (ทำให้อังกฤษผิดตาม)

เหมือน wikipedia จะอัปเดทสุด ถูกต้องที่สุด แต่ข้อมูลแยกเป็นชิ้นๆ ไม่เห็นภาพรวม ผมเลยอาสาทำ database อันนี้ขึ้นมาให้

เอาล่ะ ใครอยากโหลดฐานข้อมูลที่ผมทำไว้ก็เข้าไปได้ที่

Download ฐานข้อมูลตำบล
- https://github.com/ThepExcel/download/blob/master/ThepExcel-Thailand-Tambon.xlsx
- https://github.com/ThepExcel/download
Version History
- 16/3/2020 version 1 – ไฟล์ตั้งต้น มีตำบล อำเภอ จังหวัด ทั้งไทยและอังกฤษ
- 17/3/2020 version 2 – เพิ่มเรื่องรหัสไปรษณีย์ / แก้ชื่อภาษาอังกฤษของ ต.โคกกะเทียม เป็น Khok Kathiam
- 7/11/2021 version 3 – แก้ข้อมูลชื่อตำบลภาษาอังกฤษของอุบลราชธานีเลื่อน / แก้ชื่อ eng สมุทรปราการ กับ พังงา ตาม ISO 3166-2:TH แก้ชื่อ โพนพิสัย, กัลยาณิวัฒนา / แก้ district id เวียงเก่าเป็น 4029 / แก้ post code เฝ้าไร่
ปล. เพื่อนๆ สามารถเอาข้อมูลนี้ไปใช้ได้เลย ไม่ต้องขออนุญาตผมนะครับ

ปล. 2แน่นอนว่าฐานข้อมูลที่ผมทำมันไม่ใช่ database official อะไรทั้งสิ้น ดังนี้ใครเจอที่ผิดช่วยแจ้งที่ inbox เพจ เทพเอ็กเซลด้วย https://www.facebook.com/thepexcel/

ปล. 3 พวกข้อมูลอื่นๆ ที่มีหลายคนขอมากันเยอะๆ เช่น Latitude Longitude จริงๆ ก็มีแล้วล่ะ แต่ยังไม่ค่อยสมบูรณ์ ก็เลยขอเวลาจัดการข้อมูลให้ดีกว่านี้ก่อนค่อยเอามาปล่อยต่ออีกทีนะครับ
March 16, 2020
สูตรสำเร็จรูปสำหรับสร้าง 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 Table ด้วย DAX

แค่กด New Table ใน Power BI แล้วใส่ code นี้ลงไป
```
dDateTable = 
VAR MyCalendar=CALENDARAUTO()

RETURN

ADDCOLUMNS (MyCalendar,
"Year",YEAR([Date]),
"Quarter","Q" & QUARTER([Date]),
"Month",MONTH([Date]),
"Day",DAY([Date]),
"MonthName",FORMAT([Date],"mmmm"),
"YearMonth",FORMAT([Date],"yyyy-mm"),
"YearMonthName",FORMAT([Date],"yyyy-mmm"),
"WeekDayNum",WEEKDAY([Date],2),
"WeekDayName",FORMAT([Date],"dddd"),
"DateText",FORMAT([Date],"yyyy-mm-dd"),
"Start of Month",EOMONTH([Date],-1)+1,
"End of Month",EOMONTH([Date],0)
)
```
โดยที่ตาราง fSales มีข้อมูลรายการวันที่สั่งซื้อสินค้าอยู่ในคอลัมน์ OrderDate

ดังนั้นให้เปลี่ยนคอลัมน์วันที่ ที่จะใช้อ้างอิงได้จาก fSales[OrderDate] เป็นวันที่ใน Table ของคุณจริงๆ ได้เลย

Tips: ผมมีการ comment ใส่ startYear และ endYear อีก 2 แบบไว้ (ถ้าจะใช้ก็เอา — ข้างหน้าออกเพื่อ uncomment)
- แบบทางเลือก1 เอาไว้พิมพ์ปีที่ต้องการเริ่มและจบเอาเอง โดยที่ไม่ขึ้นกับ Field Data จริง
- แบบทางเลือก2 (ที่ยาวๆ) ซึ่งไว้เทียบ Field วันที่กรณีมีมากกว่า 1 Field ด้วย
แค่นี้ก็จะได้ Date Table มาใช้งานแล้วล่ะ

ถ้าใครจะเพิ่มคอลัมน์อีก ก็กด Add Column แล้วใส่สูตรตามปกติต่อไปได้เรื่อยๆ นะครับ เช่น วันแรก วันสุดท้ายของเดือน จำนวนวันในเดือนเป็นต้น
```
FirstDateofMonth = STARTOFMONTH(dDate[Date])
```
```
LastDateofMonth = ENDOFMONTH(dDate[Date])
```
```
NumDaysinMonth = DATEDIFF(dDate[FirstDateofMonth],dDate[LastDateofMonth],DAY)+1
```
หรือจะใช้ว่า
```
NumDaysinMonth = INT(dDate[LastDateofMonth]-dDate[FirstDateofMonth]+1)
```
ก็ได้

สร้าง Date Table ด้วย Power Query M Code

ให้เราสร้าง Blank Query (กด Get Data -> Blank Query) ขึ้นมาและใส่ Code นี้ลงไปใน Advanced Editor (กด View->Advanced Editor)

Edit : 23/3/2021 แก้ไข DayCount อันเดิมใส่วันขาดไป 1 วัน
```
(StartDate as date, EndDate as date) as table =>
let
displayRegion="en-GB",
//displayRegion="th-TH",
//StartDate=#date(2017, 1, 1),
//EndDate=#date(2019,12,31),
DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "Quarter", each "Q"& Text.From(Date.QuarterOfYear([Date])), type text),
#"Inserted Month" = Table.AddColumn(#"Inserted Quarter", "Month", each Date.Month([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.ToText([Date],"MMMM",displayRegion), type text),
#"Inserted Day" = Table.AddColumn(#"Inserted Month Name", "Day", each Date.Day([Date]), Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([Date],1)+1, Int64.Type),
#"Added YearMonthNum" = Table.AddColumn(#"Inserted Day of Week", "YearMonthNum", each Date.ToText([Date],"yyyyMM",displayRegion)),
#"Added YearMonthText" = Table.AddColumn(#"Added YearMonthNum", "YearMonthText", each Date.ToText([Date],"yyyy-MMM",displayRegion))
in
    #"Added YearMonthText"
```
Tips: สังเกตว่าใน code มีการระบุรูปแบบ region ได้ ซึ่งผมใส่เป็น displayRegion=”en-GB” ไว้ ซึ่งจะเป็นภาษาอังกฤษครับ ถ้าจะให้เป็นภาษาไทยสามารถ uncomment // เปลี่ยน displayRegion=”th-TH” ได้นะครับ

แล้วตั้งชื่อ Query ตามต้องการ เช่น CreateDateTable

ผลลัพธ์ออกมาจะเป็น Function Query ให้เราใส่ Parameter วันเริ่มกับวันจบ (กดรูปปฏิทินได้นะ)

ถ้าใส่แล้วกด invoke จะได้ตารางออกมาดังนี้

แปลว่าถ้าอยากให้ตารางนี้ Dynamic ได้จะต้องไปแก้สูตรจาก
```
= CreateDateTable(#date(2018, 1, 1), #date(2020, 12, 31))
```
ให้เป็น
```
= CreateDateTable(วันเริ่มต้น, วันสิ้นสุด)
```
โดยอาจ Link สูตรวันเริ่มต้นจากการไป Date Start of Year ของซัก Field วันที่นึง เช่น
```
วันเริ่มต้น= 
Date.StartOfYear(List.Min(fSales[OrderDate]))
```
```
วันสิ้นสุด=
Date.EndOfYear(List.Max(fSales[OrderDate]))
```
โดยที่ตาราง fSales มีข้อมูลรายการวันที่สั่งซื้อสินค้าอยู่

แค่นี้เราก็จะได้ Date Table มาใช้ และยังคง Add อะไรเพิ่มได้อีกเช่นกัน โดยคลิกที่คอลัมน์ Date และไปที่ Add Column –> Date

เช่น ผมจะใส่จำนวนวันในเดือน ก็กดปุ่มได้เลยแบบชิลๆ ไม่ต้องใส่สูตรอะไรทั้งนั้น

เท่านี้เราก็สามารถสร้าง Date Table ได้ทั้ง 2 วิธีแล้ว และก็ยังเพิ่มคอลัมน์ได้ตามที่ต้องการด้วย

ว่าแต่เพื่อนๆ ชอบแบบไหนมากกว่ากันครับ ^^
March 8, 2020
วิธีรวมไฟล์ใน 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 และ Data ไว้ โดยเลือกที่จะเอา แล้วสั่ง Remove Other Columns ซะ

ปัญหาเรื่องชื่อคอลัมน์ เกิดขึ้นตอน Expand Data

พอเรากด Expand Data ออกมา จะพบว่าสูตรที่ได้มีการระบุชื่อคอลัมน์ออกมาโต้งๆ เลย ว่า
```
= Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"วันที่", "ลูกค้า", "สินค้า", "ราคาต่อชิ้น", "จำนวนชิ้น", "วิธีการชำระเงิน"}, {"วันที่", "ลูกค้า", "สินค้า", "ราคาต่อชิ้น", "จำนวนชิ้น", "วิธีการชำระเงิน"})
```
ถ้าเป็นแบบนี้ หากในข้อมูลต้นฉบับบางคอลัมน์หายไป หรือมีคอลัมน์เพิ่มเข้ามา จะมีปัญหาทันที!!

ลองเพิ่ม/ลดคอลัมน์ดู

จะพบว่าถ้าเพิ่มคอลัมน์ เช่น ยอดขาย มันจะไม่ถูดดึงมาด้วย (เพราะสูตรไม่ได้ระบุคอลัมน์นั้น)

ถ้าลบคอลัมน์ซักอันนึง เช่น ผมเอาวิธีการชำระเงินออกออกจากทุกไฟล์ มันก็จะยังมีคอลัมน์ค้างอยู่เป็น null อย่างงั้นเลย (เพราะสูตรมีการระบุคอลัมน์นั้น)

ถ้าปล่อยให้เป็นแบบนี้ การอัปเดทข้อมูลจะยากเกินไป

แก้ปัญหาโดยแก้ M Code เล็กน้อย

เราสามารถทำให้ชื่อ Column มีความ Dynamic ยืดได้หนดได้ โดยต้องแก้บางส่วนของ Code เดิมอันนี้
```
= Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"วันที่", "ลูกค้า", "สินค้า", "ราคาต่อชิ้น", "จำนวนชิ้น", "วิธีการชำระเงิน"}, {"วันที่", "ลูกค้า", "สินค้า", "ราคาต่อชิ้น", "จำนวนชิ้น", "วิธีการชำระเงิน"})
```
จะเห็นว่า สีแดง มันคือ List ของชื่อคอลัมน์ทั้งหมด
```
= Table.ExpandTableColumn(#"Removed Other Columns", "Data", List ของชื่อคอลัมน์)
```
ซึ่ง List ของชื่อคอลัมน์เกิดจากการรวมชื่อคอลัมน์ทุก Table ดังนี้
```
List.Union(List.Transform(#"Removed Other Columns"[Data],each Table.ColumnNames(_)))
```
หลักการคือ เราจะใช้สูตร List.Transform โดยใช้ Table.ColumnNames(_) กับทุกๆ Table เพื่อให้แต่ละ Table คืนค่ามาเป็น List ของหัวตารางใน Table นั้นๆ จากนั้นใช้ List.Union มารวม item ทุกตัวเข้าด้วยกัน

ใครอยากรู้ว่าทำไมสูตรนี้ถึงได้ผล สามารถอ่านได้ที่นี่ https://www.thepexcel.com/m-code-power-query-03-list/

สรุปแล้วเราจะแก้สูตรเป็นดังนี้
```
= Table.ExpandTableColumn(#"Removed Other Columns", "Data", List.Union(List.Transform(#"Removed Other Columns"[Data],each Table.ColumnNames(_))))
```
เราก็จะได้ Column ที่ Expand ได้แบบ Dynamic สุดๆ เจ๋งป่ะ!

ผมมั่นใจว่าถ้าเพื่อนๆ ใช้วิธีที่ผมบอกนี้ ตอนอัปเดทข้อมูลชีวิตจะง่ายขึ้นเยอะเลยครับ

สุดท้าย อย่าเผลอไปกด Auto Detect Data Type นะครับ เดี๋ยวมันก็จะระบุชื่อคอลัมน์อีก ให้ทำเฉพาะคอลัมน์ที่จำเป็นเท่านั้น เช่น ตัวเลข วันที่ เวลา เป็นต้น
March 8, 2020

Cookie	Duration	Description
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.

Month: March 2020

เริ่มต้นสร้างเลข Combination

Recap

วิธีใช้ Function Query แบบแก้ไขได้

ตัวอย่างการสร้าง Function ดึงข้อมูลหุ้นจาก symbol ที่ต้องการ

1. สร้าง Parameter

2.เอา Parameter ไปใช้ใน Step Query

3.สร้างฟังก์ชัน

4.นำฟังก์ชันไปใช้งาน

กลับไปแก้ StepQuery (เพราะเราข้ามมา)

กลับไปดูผลที่ Query หลัก (หลังจากแก้ StepQuery)

ตอนต่อไปคืออะไร?

ไฟล์ประกอบ

Recap เล็กน้อย

ทำความเข้าใจ CALCULATE

ก่อนจะมีการใช้ CALCULATE

หลังจากใช้ CALCULATE

สรุปสั้นๆ นะ

CALCULATE หลายเงื่อนไข

เงื่อนไขแบบ AND

และถ้าจะเป็นเงื่อนไขแบบ OR ล่ะ?

OR Field เดียวกัน ไม่มีปัญหา

OR คนละ Field เริ่มยากละ

CALCULATE ซ้อนกัน

สารบัญ Series Power BI

หมายเหตุ

ไฟล์ประกอบ

สร้าง New Table ใหม่

DISTINCT

VALUES

ALL

กรณีอ้างอิงทั้งตาราง

กรณีอ้างอิงคอลัมน์ ไม่ว่าจะอันเดียวหรือหลายคอลัมน์

ALLEXCEPT ก็คือเพื่อนของ ALL

จุดเด่นที่สุดของ ALL คือการปลด Filter

ตัวอย่างการปลด FILTER ออก แล้ว FILTER อีกตัวกลับเข้าไปใหม่

ตอนต่อไปเจอกับ CALCULATE

สารบัญ Series Power BI

ไฟล์ประกอบ

Table Function คืออะไร?

FILTER

การอ้างอิงคอลัมน์ที่จะ FILTER

แล้วถ้าจะใส่เงื่อนไขมากกว่า 1 อย่างล่ะ?

เงื่อนไขแบบ AND

เงื่อนไขแบบ OR

ตัวอย่างการใช้ FILTER ใน Measure

สารบัญ Series Power BI

เรามาดูข้อมูลตัวอย่างกัน

มาดูการทำงานทีละฟังก์ชันก่อนนะ

UNIQUE เอาไว้คัดมาเฉพาะตัวที่ไม่ซ้ำกัน

Dynamic Array กับการงอกผลลัพธ์แบบ Spill

SORT เอาไว้เรียงข้อมูลตามที่ต้องการ

FILTER เอาไว้คัดเลือกให้เหลือเฉพาะสิ่งที่ต้องการ

ใช้ฟังก์ชันผสมผสานกัน

การเขียนฟังก์ชันซ้อนกันในสูตรเดียว

การเขียนฟังก์ชันแยก Cell กัน แต่อ้างอิงข้อมูลที่ Spill

เดี๋ยวลองผสมกัน 3 ตัวเลย

ตัวอย่างการพลิกแพลงสูตร

ตัวอย่างการสร้าง Dropdown List ที่มี Item เพิ่มตามข้อมูลที่เปลี่ยนไป

หากอยากให้สามารถเห็นข้อมูลใหม่ที่อาจเพิ่มในอนาคตได้

ตัวอย่าง เลียนแบบ Pivot Table แบบมีทั้ง Row และ Column

ตัวอย่าง เลียนแบบ Pivot Table แบบมี row 2 ชั้น

สรุป

ปัญหาของการ Merge Cell

ทำไมคนยังใช้ Merge Cell?

ทำไม Excel ถึงไม่ยอมให้เรา Sort/Filter/Pivot ข้อมูลที่ Merge?

เวลาพูดว่าอย่า Merge Cell ที่ห้ามจริงๆ หมายถึงตัว Data Input ไม่ใช่ Report Output

วิธีที่พอจะช่วยหลีกเลี่ยง Merge Cell (ถ้ายังอยากทำ)

กรณีจะ Merge แนวนอน ให้ใช้ Center Across Selection แทน

กรณีจะ Merge แนวตั้ง ให้พิมพ์ให้ครบทุกช่องเถอะ ไม่มีทางเลือก

กรณี Merge หัวตาราง พอมีทางช่วยได้

เอายังไงกับตัวสรุปรวมท้ายตาราง

Download ฐานข้อมูลตำบล

Version History

สร้าง Date Table ด้วย DAX

สร้าง Date Table ด้วย Power Query M Code

ลองทำดู

ปัญหาเรื่องชื่อคอลัมน์ เกิดขึ้นตอน Expand Data

ลองเพิ่ม/ลดคอลัมน์ดู

แก้ปัญหาโดยแก้ M Code เล็กน้อย