Thep Excel
Value.NativeQuery – รัน Native SQL Query ตรงจาก Power Query
Value.NativeQuery ใช้รันคำสั่ง SQL โดยตรงกับฐานข้อมูล เช่น SQL Server, PostgreSQL, BigQuery โดยสามารถส่ง parameters และเปิด Query Folding ได้
=Value.NativeQuery(target as any, query as text, optional parameters as any, optional options as nullable record) as any
Function Metrics
Popularity
5/10
Difficulty
7/10
Usefulness
6/10
Syntax & Arguments
=Value.NativeQuery(target as any, query as text, optional parameters as any, optional options as nullable record) as any
| Argument | Type | Required | Default | Description |
|---|---|---|---|---|
| target | any | Yes | Data source connection ที่ต้องการรัน query (เช่น ผลลัพธ์จาก Sql.Database หรือ Sql.Databases) | |
| query | text | Yes | คำสั่ง SQL หรือ native query ของ data source นั้นๆ เช่น T-SQL สำหรับ SQL Server | |
| parameters | any | Optional | null | Record หรือ List ของ parameter values สำหรับ parameterized query (ใช้ @ สำหรับ SQL Server หรือ ? สำหรับบาง database) |
| options | nullable record | Optional | null | Record ของ options เช่น [EnableFolding = true] เพื่อเปิด Query Folding หรือ [PreserveTypes = true] เพื่อรักษา data type จาก database |
Examples
ตัวอย่างพื้นฐาน – SELECT ข้อมูลตรงจากฐานข้อมูล
let Source = Sql.Database("localhost", "AdventureWorks"), Query = Value.NativeQuery( Source, "SELECT ProductID, Name, ListPrice FROM Production.Product WHERE Li…
let
Source = Sql.Database("localhost", "AdventureWorks"),
Query = Value.NativeQuery(
Source,
"SELECT ProductID, Name, ListPrice FROM Production.Product WHERE ListPrice > 100"
)
in
Query
Table ของ Product ที่มีราคามากกว่า 100
ใช้ Parameterized Query ป้องกัน SQL Injection
let Source = Sql.Database("localhost", "AdventureWorks"), MinPrice = 500, MaxPrice = 2000, Query = Value.NativeQuery( Source, "SELECT ProductID, Name, ListPrice…
let
Source = Sql.Database("localhost", "AdventureWorks"),
MinPrice = 500,
MaxPrice = 2000,
Query = Value.NativeQuery(
Source,
"SELECT ProductID, Name, ListPrice FROM Production.Product WHERE ListPrice BETWEEN @minprice AND @maxprice ORDER BY ListPrice DESC",
[minprice = MinPrice, maxprice = MaxPrice]
)
in
Query
Table ของ Product ที่ราคาระหว่าง 500-2000 จัดเรียงจากราคาสูงสุด
เปิด Query Folding ด้วย EnableFolding = true
let Source = Sql.Database("localhost", "AdventureWorks"), Query = Value.NativeQuery( Source, "SELECT * FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = 2024"…
let
Source = Sql.Database("localhost", "AdventureWorks"),
Query = Value.NativeQuery(
Source,
"SELECT * FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = 2024",
null,
[EnableFolding = true]
),
Filtered = Table.SelectRows(Query, each [TotalDue] > 10000),
Sorted = Table.Sort(Filtered, {{"OrderDate", Order.Descending}})
in
Sorted
Table ของ Orders ปี 2024 ที่ TotalDue > 10000 เรียงตามวันที่ล่าสุด (ทั้งหมดทำที่ Server)
ใช้ CTE และ Window Function (ROW_NUMBER)
let Source = Sql.Database("localhost", "AdventureWorks"), Query = Value.NativeQuery( Source, "WITH TopProducts AS ( SELECT ProductID, Name, ListPrice, CategoryI…
let
Source = Sql.Database("localhost", "AdventureWorks"),
Query = Value.NativeQuery(
Source,
"WITH TopProducts AS (
SELECT ProductID, Name, ListPrice, CategoryID,
ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY ListPrice DESC) AS RankInCategory
FROM Production.Product
)
SELECT * FROM TopProducts WHERE RankInCategory <= 5",
null,
[EnableFolding = true]
)
in
Query
Table ของ 5 สินค้าที่ราคาสูงสุดในแต่ละหมวดหมู่ พร้อม RankInCategory
FAQs
EnableFolding = true ทำอะไร?
ทำให้ step ที่ตามมา เช่น Table.SelectRows หรือ Table.Sort สามารถถูกแปลงเป็น SQL และส่งไปรันที่ Server ได้ แทนที่จะต้องโหลดข้อมูลทั้งหมดมาก่อนแล้วค่อยประมวลผลในเครื่อง ช่วยเรื่องความเร็วได้มากโดยเฉพาะถ้าข้อมูลมีหลายล้านแถว ถ้าไม่เปิด option นี้ native query จะ fold แค่ตัวมันเอง ส่วน step ถัดไปจะถูกประมวลผลใน Power Query ซึ่งช้ากว่ามากครับ
ทำไมมี warning เรื่อง Native Query?
Power Query จะแจ้งเตือนเรื่องความปลอดภัยทุกครั้งที่ใช้ native query เพราะคำสั่งที่เขียนเองอาจมีคำสั่งอันตรายที่สร้างความเสียหายต่อข้อมูลได้ เราต้องกด Edit Permission แล้วเลือก Run เพื่ออนุญาตให้รันได้ นี่เป็นเรื่องปกติและจำเป็นเพื่อความปลอดภัยของข้อมูลและระบบครับ
ใช้กับ Database อะไรได้บ้าง?
รองรับหลาย database ได้แก่ SQL Server, PostgreSQL, Oracle, MySQL, Snowflake, Google BigQuery, Amazon Redshift, SAP HANA และ Dataverse (ที่เปิด Enhanced Compute) แต่ละ database อาจมีรูปแบบการเขียน SQL และ parameter placeholder ที่แตกต่างกัน เช่น SQL Server ใช้ @param แต่บางตัวอาจใช้ ? แทน ต้องศึกษาเพิ่มเติมตาม database ที่ใช้งานครับ
ใช้กับ DirectQuery ใน Power BI ได้ไหม?
ไม่ได้ครับ Value.NativeQuery ใช้ได้เฉพาะ Import mode เท่านั้น ถ้าพยายามใช้กับ DirectQuery จะได้ข้อความแสดงข้อผิดพลาด ถ้าต้องการใช้ native query ใน DirectQuery ให้ตั้งค่าตอนเชื่อมต่อ database แทน โดยเลือกใส่ native query ใน connector settings ตั้งแต่ต้นครับ
Parameters สามารถเปลี่ยนตาม Slicer ได้ไหม?
ไม่ได้โดยตรงครับ เพราะ parameters ต้องกำหนดค่าตอนที่ query ถูกประมวลผล ซึ่งเกิดขึ้นก่อนที่รายงานจะโหลด ถ้าต้องการให้ผู้ใช้สามารถเลือกกรองข้อมูลจาก slicer ได้ แนะนำให้ใช้ DAX filter แทน หรือถ้าข้อมูลไม่ใหญ่มากก็โหลดข้อมูลทั้งหมดมาแล้วใช้ slicer filter ใน report แทนครับ
Resources & Related
Resources
Additional Notes
Value.NativeQuery ใช้รันคำสั่ง Native Query (เช่น T-SQL) โดยตรงกับ Data Source จาก Power Query
ทำไมต้องใช้ Value.NativeQuery? เพราะบางครั้ง Power Query UI ไม่รองรับ feature ของ database โดยตรง เช่น Window Functions, CTE, หรือ stored procedures – ฟังก์ชันนี้ช่วยให้เราเขียน SQL เองได้เต็มที่ และที่สำคัญคือสามารถเปิด Query Folding ได้ด้วย!
ที่เจ๋งคือเราสามารถเปิด EnableFolding = true ได้ ทำให้ step ที่ตามมา (เช่น filter, sort) จะถูกแปลงเป็น SQL และส่งไปรันที่ Server แทนที่จะโหลดข้อมูลทั้งหมดมาก่อน ช่วยเรื่อง performance ได้มากครับ
ส่วนตัวผมใช้ฟังก์ชันนี้ตอนที่ต้องการควบคุม SQL Query เองแบบเต็มที่ หรือต้องใช้ feature ของ database ที่ Power Query ไม่รองรับ เช่น ROW_NUMBER, CTE หรือ complex JOIN patterns 😎