Excel ปลายทางข้อมูลขั้นสูง

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

บทความนี้อธิบายวิธีการสร้างตารางการนําทางเหล่านี้เพื่อสร้างเอกสาร Excel โดยทางโปรแกรมจากกระแสข้อมูลของคุณ

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

ข้อกำหนดเบื้องต้น

ก่อนที่คุณจะสามารถใช้ปลายทางขั้นสูงของ Excel คุณต้องตั้งค่ากระแสข้อมูล Gen2 ของคุณด้วยการกําหนดค่าที่ถูกต้อง:

1. สร้างคิวรีตารางการนําทาง - ใน Dataflow Gen2 ของคุณ ให้สร้างคิวรีที่ส่งออกตารางการนําทางตามที่อธิบายไว้ในบทความนี้ แบบสอบถามควรส่งกลับตารางที่มีคอลัมน์ที่จําเป็น (PartType, , และ DataSheet, และ NameProperties)

2. เพิ่มปลายทางข้อมูล - เลือกคิวรีตารางการนําทางของคุณและเพิ่มปลายทางข้อมูลโดยเลือก เพิ่มปลายทางข้อมูล จากเมนูบริบทของคิวรีหรือริบบิ้น

3. เลือกปลายทางที่อิงตามไฟล์ - เลือกปลายทางที่อิงตามไฟล์ เช่น:

   • SharePoint
   • ไฟล์เลคเฮาส์
   • Azure Data Lake Storage รุ่น2
   • ที่เก็บข้อมูล Azure Blob

4. ตั้งค่ารูปแบบไฟล์เป็น Excel - ในการกําหนดค่าปลายทาง ให้ตั้งค่า รูปแบบไฟล์ เป็น Excel

5. เลือก รูปแบบขั้นสูง - ในดรอปดาวน์ รูปแบบ ให้เลือก ขั้นสูง ตัวเลือกนี้จะบอกให้ตัวเชื่อมต่อตีความผลลัพธ์คิวรีของคุณเป็นตารางการนําทางที่กําหนดโครงสร้างเอกสาร Excel

   

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

ภาพรวม

เอกสาร Excel ใน Power Query จะแสดงเป็นตารางนําทาง ซึ่งเป็นตาราง M มาตรฐานที่มีคอลัมน์เฉพาะที่อธิบายโครงสร้างเอกสาร แต่ละแถวในตารางนําทางแสดงถึงส่วนหนึ่งของเอกสาร Excel เช่น แผ่นงานที่มีข้อมูลหรือแผนภูมิที่แสดงภาพข้อมูลนั้น

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

ตัวอย่างที่ใช้ชื่อเช่น SalesData, YourDataTable, หรือ MainTable คาดหวังว่าค่าเหล่านั้นจะเป็นตารางที่กําหนดไว้ก่อนหน้านี้ในคําสั่งเดียวกัน หรือถูกส่งมาจากคําสั่ง Dataflow อื่น การค้นหาที่สมบูรณ์ต้องกําหนดตัวระบุอ้างอิงทุกตัว

การอ้างอิงด่วน

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

ประเภทชิ้นส่วนได้อย่างรวดเร็ว

คุณสมบัติหลักตามชนิดชิ้นส่วน

* สําหรับแผนภูมิที่มีข้อมูลแบบอินไลน์ คุณสมบัติเหล่านี้จะควบคุมตารางข้อมูลสํารอง

ตัวอย่างขั้นต่ํา

วิธีที่ง่ายที่สุดในการสร้างเอกสาร Excel คือการจัดเตรียมตารางการนําทางที่มีเฉพาะข้อมูลที่คุณต้องการส่งออก PartType และ Data เป็นคอลัมน์เดียวที่จําเป็นสําหรับตารางนําทาง ตัวเชื่อมต่อสามารถอนุมานคอลัมน์ที่ถูกSheetละเว้น , Name, และProperties

ตัวอย่างต่อไปนี้กําหนด SalesData ตารางที่ใช้ตลอดทั้งบทความนี้:

let
    // Define your data with columns used throughout this article
    SalesData = #table(
        type table [
            Region = text, 
            Category = text, 
            Product = text, 
            Quarter = text,
            Revenue = number,
            Units = number
        ],
        {
            {"North", "Electronics", "Laptop", "Q1", 45000, 150},
            {"North", "Electronics", "Phone", "Q1", 32000, 400},
            {"North", "Furniture", "Desk", "Q1", 18000, 60},
            {"South", "Electronics", "Laptop", "Q1", 38000, 120},
            {"South", "Electronics", "Phone", "Q1", 28000, 350},
            {"South", "Furniture", "Chair", "Q1", 12000, 200},
            {"East", "Electronics", "Tablet", "Q2", 22000, 180},
            {"East", "Furniture", "Desk", "Q2", 15000, 50},
            {"West", "Electronics", "Phone", "Q2", 41000, 520},
            {"West", "Furniture", "Chair", "Q2", 9000, 150}
        }
    ),

    // Create the navigation table with minimal structure
    // Only Data and PartType are required
    excelDocument = #table(
        type table [PartType = nullable text, Data = any],
        {
            {"SheetData", SalesData}
        }
    )
in
    excelDocument

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

การอ้างอิงคิวรีอื่นๆ

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

let
    // Reference existing queries named "MonthlySales" and "CurrentInventory"
    excelDocument = #table(
        type table [PartType = nullable text, Data = any],
        {
            {"SheetData", MonthlySales},
            {"SheetData", CurrentInventory}
        }
    )
in
    excelDocument

โครงสร้างตารางการนําทาง

ตารางการนําทางเป็นไปตาม Schema เฉพาะที่มีคอลัมน์ต่อไปนี้:

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

type table [
    Sheet = nullable text,
    Name = nullable text,
    PartType = text,
    Properties = nullable record,
    Data = any
]

คําสั่งเก่าหรือแบบรวมแบบไดนามิกสามารถประกาศคอลัมน์เป็น PartType = nullable text;ระบบยอมรับการประกาศประเภท M นี้ แต่ทุกแถวในตารางนําทางต้องมีค่าที่ไม่เป็นศูนย์PartType ใช้[]สําหรับระเบียนที่รวมมาแต่ว่างเปล่าProperties คุณค่าทางกฎหมาย Data มีดังนี้:

ชนิดชิ้นส่วนที่รองรับ

• เวิร์กบุ๊ก: การกําหนดค่าระดับเอกสาร ต้องเป็นแถวแรกหากใช้ ไม่มีข้อมูล
• SheetData: แผ่นงานที่มีข้อมูลแบบตาราง ส่วนนี้ถูกผูกไว้กับข้อมูล ส่วน SheetData ต้องมีข้อมูลแบบอินไลน์และไม่สามารถใช้การอ้างอิงตารางได้ สร้างช่วงข้อมูลเดียวโดยเริ่มจากเซลล์ A1
• ตาราง: ตาราง Excel (จัดรูปแบบด้วยการจัดรูปแบบตาราง) ส่วนนี้ถูกผูกไว้กับข้อมูล รองรับการวางตําแหน่งที่ชัดเจนผ่าน StartCell และการวางตําแหน่งอัตโนมัติ ส่วนของตารางหลายส่วนสามารถมีอยู่ในแผ่นงานเดียวกันได้หากไม่ทับซ้อนกัน
• ช่วง: ช่วงข้อมูลที่ไม่มีการจัดรูปแบบตาราง Excel ส่วนนี้ถูกผูกไว้กับข้อมูล รองรับการวางตําแหน่งที่ชัดเจนผ่าน StartCell และการวางตําแหน่งอัตโนมัติ ส่วนช่วงหลายส่วนสามารถมีอยู่ในแผ่นงานเดียวกันได้หากไม่ทับซ้อนกัน
• ข้อความ: ค่าเดียวที่วางไว้ในเซลล์หรือพื้นที่ที่รวมกัน รองรับการวางตําแหน่งที่ชัดเจนผ่าน StartCell และการวางตําแหน่งอัตโนมัติ ข้อความไม่ได้ถูกผูกกับข้อมูลและไม่สามารถอ้างอิงด้วยแผนภูมิได้
• แผนภูมิ: การแสดงภาพแผนภูมิ สามารถวางบนแผ่นงานของตัวเองหรือรวมกับข้อมูล ส่วนนี้ถูก ผูกมัดด้วยข้อมูล เมื่อมีข้อมูลแบบอินไลน์ หรือสามารถอ้างอิงข้อมูลจากส่วนอื่นได้

การเลือกประเภทชิ้นส่วนที่เหมาะสม

ใช้คู่มือการตัดสินใจนี้เพื่อเลือกชนิดชิ้นส่วนที่เหมาะสมสําหรับสถานการณ์ของคุณ:

ใช้ SheetData เมื่อ:

• คุณต้องมีขอบเขตข้อมูลเดียวที่เรียบง่ายต่อแผ่นงาน
• ข้อมูลควรเริ่มต้นที่เซลล์ A1
• คุณไม่จําเป็นต้องมีคุณลักษณะของตาราง Excel (ตัวกรอง การอ้างอิงที่มีโครงสร้าง)
• คุณกําลังส่งออกข้อมูลแบบตารางที่ตรงไปตรงมา

ใช้ Table เมื่อ:

• คุณต้องการการจัดรูปแบบตาราง Excel พร้อมดรอปดาวน์ตัวกรอง
• คุณต้องการการอ้างอิงที่มีโครงสร้างสําหรับสูตร
• คุณกําลังวางขอบเขตข้อมูลหลายเขตบนแผ่นงานเดียว
• คุณต้องการควบคุมตําแหน่งที่แม่นยําด้วย StartCell
• คุณต้องการใช้ลักษณะตาราง (TableStyleMedium1ฯลฯ)

ใช้ Range เมื่อ:

• คุณต้องการข้อมูลดิบโดยไม่ต้องจัดรูปแบบตาราง Excel
• คุณต้องการละเว้นส่วนหัว (SkipHeader = true)
• คุณกําลังวางขอบเขตข้อมูลหลายเขตบนแผ่นงานเดียว
• ข้อมูลจะถูกใช้โดยระบบอื่นที่คาดว่าจะมีช่วงธรรมดา

ใช้ Text เมื่อ:

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

ใช้ Chart เมื่อ:

• คุณต้องการการแสดงข้อมูลเป็นภาพ
• เมื่อใช้ข้อมูลแบบอินไลน์: แผนภูมิและข้อมูลจะปรากฏพร้อมกันในแผ่นงานเดียวกัน
• ด้วยการอ้างอิง: แผนภูมิและข้อมูลสามารถอยู่ในแผ่นงานแยกกัน และหลายแผนภูมิสามารถแชร์แหล่งข้อมูลเดียวได้

ตัวอย่างที่มีโครงสร้างเต็มรูปแบบ

let
    // Helper function to create a reference to another part
    buildReference = (name as text) => #table({}, {}) meta [Name = name],

    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {null, null, "Workbook", [ChartInferenceFunction = Office.InferChartPropertiesGenerator()], null},
            {"DataSheet", "SalesTable", "SheetData", [], SalesData},
            {"ChartSheet", "SalesChart", "Chart", [ChartType = "Column"], buildReference("SalesTable")}
        }
    )
in
    excelDocument

แผนภูมิและการจัดวางข้อมูล

เมื่อคุณสร้างส่วนของแผนภูมิ คุณสามารถควบคุมตําแหน่งที่แผนภูมิและข้อมูลของแผนภูมิจะปรากฏในเวิร์กบุ๊กได้

แผนภูมิที่มีแผ่นข้อมูลแยกต่างหาก

เมื่อแผนภูมิอ้างอิงข้อมูลจากส่วนอื่นโดยใช้ meta [Name = "..."] ไวยากรณ์ ข้อมูลและแผนภูมิจะถูกวางไว้ในแผ่นงานที่แยกจากกัน

let
    // Helper function to create a reference to another part
    buildReference = (name as text) => #table({}, {}) meta [Name = name],

    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {null, null, "Workbook", [ChartInferenceFunction = Office.InferChartPropertiesGenerator()], null},
            // Data on its own sheet
            {"DataSheet", "SalesTable", "SheetData", [], SalesData},
            // Chart on a different sheet, referencing the data
            {"ChartSheet", "SalesChart", "Chart", [ChartType = "Line"], buildReference("SalesTable")}
        }
    )
in
    excelDocument

แผนภูมิที่มีข้อมูลแบบอินไลน์

เมื่อส่วนของแผนภูมิมีข้อมูลโดยตรง (โดยไม่อ้างอิงส่วนอื่น) ทั้งข้อมูลและแผนภูมิจะถูกสร้างขึ้นในแผ่นงานเดียวกัน:

let
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {null, null, "Workbook", [ChartInferenceFunction = Office.InferChartPropertiesGenerator()], null},
            // Data and chart appear together on the same sheet
            {"SalesSheet", "SalesChart", "Chart", [ChartType = "Column"], SalesData}
        }
    )
in
    excelDocument

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

แผนภูมิหลายแผนภูมิที่มีข้อมูลแบบอินไลน์บนแผ่นงานเดียวกัน

เมื่อส่วนแผนภูมิหลายส่วนมีข้อมูลแบบอินไลน์ (ไม่ใช่การอ้างอิง) แต่ละแผนภูมิจะสร้างตารางข้อมูลสํารองของตัวเองบนแผ่นงานเดียวกัน ระบบจะจัดตําแหน่งโต๊ะสํารองเหล่านี้โดยอัตโนมัติเพื่อหลีกเลี่ยงการทับซ้อนกันโดยใช้การวางตําแหน่งอัตโนมัติ คุณสามารถควบคุมลักษณะที่ปรากฏของตารางสํารองได้โดยการระบุ TableStyle, AutoPositionColumnOffset, และ AutoPositionRowOffset ในส่วนของแผนภูมิ

let
    ProductData = #table(
        type table [Product = text, Sales = number],
        {{"Widget", 100}, {"Gadget", 200}, {"Gizmo", 150}}
    ),

    RegionData = #table(
        type table [Region = text, Revenue = number],
        {{"North", 5000}, {"South", 3000}, {"East", 4000}}
    ),

    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            // First chart with inline data - backing table auto-positioned
            {"Dashboard", "ProductChart", "Chart",
                [
                    Bounds = "B2:H15",
                    ChartType = "Column",
                    ChartTitle = "Product Sales",
                    DataSeries = [AxisColumns = {"Product"}, ValueColumns = {"Sales"}]
                ],
                ProductData
            },
            // Second chart with inline data - backing table auto-positioned after first
            {"Dashboard", "RegionChart", "Chart",
                [
                    Bounds = "J2:P15",
                    ChartType = "Pie",
                    ChartTitle = "Revenue by Region",
                    TableStyle = "TableStyleLight1",  // Style for the backing table
                    AutoPositionRowOffset = 2,        // Add extra row gap from previous table
                    DataSeries = [AxisColumns = {"Region"}, ValueColumns = {"Revenue"}]
                ],
                RegionData
            }
        }
    )
in
    excelDocument

ชิ้นส่วนตารางและช่วง

TableและRangeชิ้นส่วนให้การควบคุมการจัดวางข้อมูลได้มากกว่าSheetData ใช้เมื่อคุณต้องการขอบเขตข้อมูลหลายเขตบนแผ่นงานเดียวกันหรือต้องการการวางตําแหน่งที่เฉพาะเจาะจง

ส่วนโต๊ะ

ส่วนนี้ Table สร้างตาราง Excel พร้อมการจัดรูปแบบ ดรอปดาวน์ตัวกรอง และการอ้างอิงที่มีโครงสร้าง ใช้เมื่อคุณต้องการคุณลักษณะตาราง Excel

let
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            // Table with explicit position and custom style
            {"Sales", "SalesTable", "Table",
                [StartCell = "B3", TableStyle = "TableStyleMedium9"],
                SalesData
            }
        }
    )
in
    excelDocument

ส่วนช่วง

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

let
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            // Range at a specific position
            {"Data", "DataRange", "Range",
                [StartCell = "D5"],
                SalesData
            }
        }
    )
in
    excelDocument

ส่วนข้อความ

ชิ้นส่วนจะ Text ใส่ค่าหนึ่งค่าในเวิร์กชีต ใช้เซลล์ StartCell เดียวสําหรับเซลล์ปกติ หรือใช้ช่วงที่มีขอบเขตจํากัด เช่น "A1:B1" เพื่อรวมพื้นที่นั้นและวางค่าไว้ในเซลล์บนซ้าย

ใช้อย่างใดอย่างหนึ่ง StartCell ไม่ใช่ ColumnSpanทั้งสองอย่าง ColumnSpan สร้างพื้นที่รวมแนวนอนและใช้ได้เฉพาะกับการวางตําแหน่งอัตโนมัติเท่านั้น เพื่อสร้างพื้นที่รวมหลายแถว ให้ระบุช่วงที่มีขอบเขตสมบูรณ์ใน StartCellเช่น "A1:B2".

ข้อความสามารถแชร์แผ่นงานกับส่วนตาราง ช่วง และแผนภูมิเมื่อเซลล์ที่ครอบครองไม่ทับซ้อนกัน มันไม่สามารถแชร์ชีตกับ SheetData, และไม่สามารถใช้เป็นแหล่งข้อมูลกราฟได้

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

let
    SalesData = #table(
        type table [Region = text, Revenue = number],
        {{"North", 95000}, {"South", 78000}}
    ),

    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"Report", "ReportTitle", "Text", [StartCell = "A1:B1", ShowGridlines = false], "Regional Sales Report"},
            {"Report", "SalesTable", "Table", [StartCell = "A3"], SalesData}
        }
    )
in
    excelDocument

หลายตารางบนแผ่นงานเดียวกัน

คุณสามารถวางหลาย Tableส่วน , Range, หรือ Text หลายส่วนบนแผ่นเดียวกันได้ ตราบใดที่ไม่ทับซ้อนกัน

let
    SummaryData = #table(type table [Metric = text, Value = number], {{"Total", 50000}, {"Average", 5000}}),

    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            // Two tables side by side
            {"Report", "DetailTable", "Table",
                [StartCell = "A1", TableStyle = "TableStyleMedium9"],
                SalesData
            },
            {"Report", "SummaryTable", "Table",
                [StartCell = "I1", TableStyle = "TableStyleLight15"],
                SummaryData
            }
        }
    )
in
    excelDocument

การวางตําแหน่งอัตโนมัติ

เมื่อคุณไม่ได้ระบุ StartCell (หรือตั้งค่าเป็น "auto") ระบบจะจัดตําแหน่งชิ้นส่วนในแนวตั้งโดยอัตโนมัติ โดยแต่ละส่วนอยู่ด้านล่างอีกส่วนหนึ่ง ตามลําดับที่ปรากฏในตารางการนําทาง สิ่งนี้มีประโยชน์เมื่อคุณมีขอบเขตข้อมูลหลายแห่งและไม่ต้องการการควบคุมที่แม่นยํา

let
    DataA = #table(type table [X = number], {{1}, {2}, {3}}),
    DataB = #table(type table [Y = number], {{10}, {20}}),
    DataC = #table(type table [Z = number], {{100}}),

    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            // First table: starts at B2 (column offset 1, row offset 1)
            {"AutoSheet", "Table1", "Table", [], DataA},
            // Second table: starts at B + (3 data rows + 1 header + 1 gap) = B7
            {"AutoSheet", "Table2", "Table", [], DataB},
            // Third table: continues below Table2
            {"AutoSheet", "Table3", "Table", [], DataC}
        }
    )
in
    excelDocument

ในการเปลี่ยนลักษณะการทํางานของตําแหน่ง:

let
    DataA = #table(type table [X = number], {{1}, {2}, {3}}),
    DataB = #table(type table [Y = number], {{10}, {20}}),

    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            // Start at column C (offset 2), row 4 (offset 3)
            {"CustomOffset", "Table1", "Table",
                [AutoPositionColumnOffset = 2, AutoPositionRowOffset = 3],
                DataA
            },
            // Start at column A (offset 0), with 2 row gap
            {"CustomOffset", "Table2", "Table",
                [AutoPositionColumnOffset = 0, AutoPositionRowOffset = 2],
                DataB
            }
        }
    )
in
    excelDocument

ทับซ้อนกัน

ระบบจะตรวจจับและรายงานช่วงที่ทับซ้อนกัน หากสองส่วนจะเขียนลงในเซลล์เดียวกัน จะมีการโยนข้อผิดพลาด

เพื่อหลีกเลี่ยงการทับซ้อนกัน:

• ใช้การวางตําแหน่งอัตโนมัติและปล่อยให้ระบบจัดเรียงชิ้นส่วนในแนวตั้ง
• เมื่อใช้ค่าที่ชัดเจน StartCell ให้ตรวจสอบให้แน่ใจว่าส่วนต่างๆ ไม่ได้ใช้เซลล์ร่วมกัน
• พิจารณาแถวส่วนหัวเมื่อคํานวณตําแหน่ง (ตารางจะมีแถวส่วนหัวเสมอ แต่ในช่วงจะเป็นทางเลือก)

ความกว้างของคอลัมน์

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

คุณสมบัติ ShowGridlines

คุณสมบัติจะ ShowGridlines ควบคุมว่าเส้นตารางของ Excel จะมองเห็นได้สําหรับแผ่นงานหรือไม่ ตั้งค่าคุณสมบัตินี้บน SheetData, Table, Range, Text, หรือ Chart ส่วนต่าง ๆ มันส่งผลกระทบกับทั้งแผ่น เมื่อ true (ค่าเริ่มต้น) เส้นตารางจะมองเห็นได้ เมื่อ falseเส้นตารางถูกซ่อนไว้

ถ้าส่วนใดส่วนหนึ่งบนแผ่นงานตั้งค่าเป็น ShowGridlinesทั้งแผ่นงานจะซ่อนเส้นตารางไว้อย่างชัดเจนfalse

let
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            // Hide gridlines for this sheet
            {"CleanReport", "DataTable", "Table",
                [ShowGridlines = false, StartCell = "B2"],
                SalesData
            }
        }
    )
in
    excelDocument

คุณสมบัติของสมุดงาน

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

ตัวอย่างที่มีคุณสมบัติของเวิร์กบุ๊ก

let
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            // Workbook configuration (must be first)
            {null, null, "Workbook",
                [
                    ChartInferenceFunction = Office.InferChartPropertiesGenerator([
                        Allow3DCharts = false,
                        PreferMultilevelChartInference = true
                    ]),
                    StrictNameHandling = false
                ], 
                null
            },
            // Data and charts follow
            {"Sales", "SalesTable", "SheetData", [], SalesData}
        }
    )
in
    excelDocument

แผนภูมิหลายแผนภูมิที่มีข้อมูลที่ใช้ร่วมกัน

แผนภูมิสามารถอ้างอิงแหล่งข้อมูลเดียวกัน ซึ่งช่วยให้คุณสามารถสร้างการแสดงภาพหลายรายการของชุดข้อมูลเดียวกันได้โดยไม่ต้องทําซ้ําข้อมูลในเวิร์กบุ๊ก

ใช้ไว meta [Name = "..."] ยากรณ์เพื่อสร้างการอ้างอิง Nameต้องระบุคอลัมน์สําหรับส่วนข้อมูลเพื่อให้แผนภูมิสามารถแก้ไขการอ้างอิงได้

let
    // Helper function to create a reference
    buildReference = (name as text) => #table({}, {}) meta [Name = name],

    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            // Single data source - Name is required for reference resolution
            {"Data", "SalesTable", "SheetData", [], SalesData},
            
            // Multiple charts referencing the same data
            // Each chart must specify DataSeries with AxisColumns and ValueColumns
            {"LineChart", "TrendChart", "Chart", 
                [
                    ChartType = "Line", 
                    ChartTitle = "Sales Trend",
                    DataSeries = [AxisColumns = {"Quarter"}, ValueColumns = {"Revenue"}]
                ],
                buildReference("SalesTable")
            },
            {"PieChart", "DistributionChart", "Chart",
                [
                    ChartType = "Pie", 
                    ChartTitle = "Sales Distribution",
                    DataSeries = [AxisColumns = {"Region"}, ValueColumns = {"Revenue"}]
                ],
                buildReference("SalesTable")
            },
            {"BarChart", "ComparisonChart", "Chart",
                [
                    ChartType = "Bar", 
                    ChartTitle = "Product Comparison",
                    DataSeries = [AxisColumns = {"Product"}, ValueColumns = {"Revenue", "Units"}]
                ],
                buildReference("SalesTable")
            }
        }
    )
in
    excelDocument

การอ้างอิงทํางานทั้งในทิศทางไปข้างหน้าและถอยหลัง แผนภูมิสามารถอ้างอิงข้อมูลที่กําหนดไว้ในตารางในภายหลัง และแผนภูมิหลายแผนภูมิสามารถอ้างอิงแหล่งข้อมูลเดียวกันได้

แง่มุมของชนิดและการจัดรูปแบบเซลล์

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

การแม็ปชนิดพื้นฐาน

การใช้แง่มุมของตัวอักษรเพื่อการจัดรูปแบบที่แม่นยํา

แง่มุมของประเภทช่วยให้คุณสามารถระบุความแม่นยําและมาตราส่วนสําหรับค่าตัวเลข:

let
    // Define types with specific facets
    currencyType = Type.ReplaceFacets(Currency.Type, 
        [NumericPrecisionBase = 10, NumericPrecision = 19, NumericScale = 4]),
    
    percentageType = Type.ReplaceFacets(Percentage.Type, 
        [NumericPrecisionBase = 10, NumericPrecision = 5, NumericScale = 2]),
    
    decimalType = Type.ReplaceFacets(Decimal.Type, 
        [NumericPrecisionBase = 10, NumericPrecision = 10, NumericScale = 4]),

    // Create table with typed columns
    tableType = type table [
        Product = Text.Type,
        Price = currencyType,           // Displays as currency with 4 decimal places
        Discount = percentageType,       // Displays as percentage with 2 decimal places
        TaxRate = decimalType           // Displays as number with 4 decimal places
    ],

    pricingData = #table(tableType, {
        {"Widget", 29.9999, 0.15, 0.0825},
        {"Gadget", 49.9500, 0.20, 0.0825}
    }),

    // Create navigation table with the typed data
    excelDocument = #table(
        type table [PartType = nullable text, Data = any],
        {
            {"SheetData", pricingData}
        }
    )
in
    excelDocument

ความแม่นยําของวันที่และเวลา

คุณสามารถควบคุมความแม่นยําของส่วนประกอบเวลาได้โดยใช้ DateTimePrecision แง่มุม:

let
    // Time with millisecond precision
    timeWithMs = Type.ReplaceFacets(Time.Type, [DateTimePrecision = 3]),
    
    // DateTime with microsecond precision (Excel maximum is 3 digits)
    dateTimeWithPrecision = Type.ReplaceFacets(DateTime.Type, [DateTimePrecision = 7]),

    tableType = type table [
        EventName = Text.Type,
        EventTime = timeWithMs,
        Timestamp = dateTimeWithPrecision
    ],

    eventsData = #table(tableType, {
        {"Start", #time(9, 30, 15.123), #datetime(2025, 1, 15, 9, 30, 15.1234567)},
        {"End", #time(17, 45, 30.456), #datetime(2025, 1, 15, 17, 45, 30.9876543)}
    }),

    // Create navigation table with the typed data
    excelDocument = #table(
        type table [PartType = nullable text, Data = any],
        {
            {"SheetData", eventsData}
        }
    )
in
    excelDocument

การกําหนดค่าแผนภูมิ

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

คุณสมบัติของแผนภูมิ

การวางตําแหน่งแผนภูมิ

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

การวางตําแหน่งเริ่มต้น

เมื่อ Bounds ไม่ได้ระบุ แผนภูมิจะถูกวางตําแหน่งที่ตําแหน่งเริ่มต้นโดยเริ่มจากเซลล์ H8 โดยมีขนาดเริ่มต้นกว้าง 8 คอลัมน์และสูง 16 แถว แผนภูมิหลายแผนภูมิในแผ่นงานเดียวกันโดยไม่มีการ Bounds ชัดแจ้งจะทับซ้อนกันที่ตําแหน่งเริ่มต้นนี้

รูปแบบข้อความ (ไวยากรณ์ช่วง Excel)

ใช้การอ้างอิงเซลล์เดียวสําหรับมุมซ้ายบน (ขนาดเริ่มต้น 8 คอลัมน์ x 16 แถว) หรือช่วงสําหรับขนาดที่ชัดเจน:

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

// Single cell: chart starts at B2 with default size
[Bounds = "B2"]

// Range: chart fills the area from G6 to N21
[Bounds = "G6:N21"]

รูปแบบการบันทึกด้วยความกว้างและความสูง

ระบุมุมซ้ายบนและขนาด:

ใช้โหมดนี้เมื่อคุณทราบขนาดกราฟ อย่ารวมToColumnหรือToRowอยู่ในบันทึกเดียวกันBounds

[Bounds = [
    FromColumn = "B",   // or 1 for column B
    FromRow = 1,        // Row 2 (0-based)
    Width = 8,          // 8 columns wide
    Height = 16         // 16 rows tall
]]

รูปแบบการบันทึกที่มีมุมที่ชัดเจน

หรือระบุมุมทั้งสองของพื้นที่แผนภูมิ:

ใช้โหมดนี้เมื่อคุณทราบตําแหน่งสมอทั้งสองตําแหน่ง อย่ารวมWidthหรือHeightอยู่ในบันทึกเดียวกันBounds FromColumn, FromRow, , และ ToRow เป็นพิกัดจุดยึดที่มีฐานเป็นศูนย์; To พิกัดต้องมากกว่าพิกัดที่สอดคล้องกันFromToColumn

[Bounds = [
    FromColumn = "A",
    FromRow = 0,
    ToColumn = "H",
    ToRow = 16
]]

ฟังก์ชันตัวช่วยเค้าโครงกริด

เมื่อวางแผนภูมิหลายแผนภูมิบนแผ่นงานคุณสามารถใช้ฟังก์ชันตัวช่วยเพื่อคํานวณตําแหน่งกริด:

let
    // Helper function to calculate chart bounds in a grid layout
    GetGridBounds = (
        chartNumber as number,       // 1-based chart number
        gridColumns as number,       // Number of columns in the grid
        optional width as number,    // Chart width (default: 8)
        optional height as number,   // Chart height (default: 16)
        optional hPadding as number, // Horizontal padding (default: 1)
        optional vPadding as number  // Vertical padding (default: 1)
    ) as record =>
        let
            w = width ?? 8,
            h = height ?? 16,
            hp = hPadding ?? 1,
            vp = vPadding ?? 1,
            cols = if gridColumns < 1 then 1 else gridColumns,

            x = Number.Mod(chartNumber - 1, cols) * (w + hp),
            y = Number.IntegerDivide(chartNumber - 1, cols) * (h + vp)
        in
            [FromColumn = x, FromRow = y, ToColumn = x + w, ToRow = y + h],

    buildReference = (name as text) => #table({}, {}) meta [Name = name],

    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"Data", "SalesTable", "SheetData", [], SalesData},
            // 2x2 grid of charts
            {"Dashboard", "Chart1", "Chart",
                [ChartType = "Column", Bounds = GetGridBounds(1, 2),
                 DataSeries = [AxisColumns = {"Region"}, ValueColumns = {"Revenue"}]],
                buildReference("SalesTable")},
            {"Dashboard", "Chart2", "Chart",
                [ChartType = "Line", Bounds = GetGridBounds(2, 2),
                 DataSeries = [AxisColumns = {"Region"}, ValueColumns = {"Revenue"}]],
                buildReference("SalesTable")},
            {"Dashboard", "Chart3", "Chart",
                [ChartType = "Pie", Bounds = GetGridBounds(3, 2),
                 DataSeries = [AxisColumns = {"Region"}, ValueColumns = {"Revenue"}]],
                buildReference("SalesTable")},
            {"Dashboard", "Chart4", "Chart",
                [ChartType = "Bar", Bounds = GetGridBounds(4, 2),
                 DataSeries = [AxisColumns = {"Region"}, ValueColumns = {"Revenue"}]],
                buildReference("SalesTable")}
        }
    )
in
    excelDocument

การวางตําแหน่งขั้นสูงด้วยออฟเซ็ต EMU

สําหรับการวางตําแหน่งที่แม่นยําภายในเซลล์ ให้ใช้คุณสมบัติออฟเซ็ต ออฟเซ็ตอยู่ใน EMU (หน่วยเมตริกภาษาอังกฤษ) โดยที่ 914400 EMU เท่ากับ 1 นิ้ว

[Bounds = [
    FromColumn = "C",
    FromColumnOffset = 352425,
    FromRow = 6,
    FromRowOffset = 142874,
    ToColumn = "R",
    ToColumnOffset = 142875,
    ToRow = 22,
    ToRowOffset = 171449
]]

ชนิดแผนภูมิที่รองรับ

การกําหนดค่า DataSeries

DataSeriesเรกคอร์ดจะกําหนดวิธีที่ข้อมูลของคุณแมปกับองค์ประกอบแผนภูมิ:

* จําเป็น เว้นแต่จะมีการกําหนดค่า a ChartInferenceFunction ในคุณสมบัติของเวิร์กบุ๊ก หากไม่มีการอนุมาน การ AxisColumns ละเว้นจะทําให้เกิดข้อผิดพลาด "ไม่มีคอลัมน์แกนให้" และการละเว้น ValueColumns จะทําให้เกิดข้อผิดพลาด "ไม่มีคอลัมน์ค่าให้"

AxisColumnsและValueColumnsระบุคอลัมน์ในสคีมาตาราง Power Query ต้นทางของแผนภูมิ ชื่อของพวกเขาต้องแยกตัวพิมพ์เล็กและต้องตรงกับสคีมานั้นอย่างแม่นยํา มันไม่ใช่ตัวอักษรคอลัมน์ในแผ่นงาน Excel

ตัวอย่างที่มีการกําหนดค่าแผนภูมิที่ชัดเจน

let
    // Quarterly data by region
    quarterlyData = #table(
        type table [Quarter = text, North = number, South = number, East = number, West = number],
        {
            {"Q1", 95000, 78000, 37000, 50000},
            {"Q2", 102000, 85000, 42000, 58000}
        }
    ),

    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"SalesChart", "QuarterlySales", "Chart",
                [
                    ChartType = "StackedColumn",
                    ChartTitle = "Quarterly Revenue by Region",
                    DataSeries = [
                        AxisColumns = {"Quarter"},
                        ValueColumns = {"North", "South", "East", "West"}
                    ]
                ],
                quarterlyData
            }
        }
    )
in
    excelDocument

แผนภูมิแกนหลายระดับ

สําหรับข้อมูลประเภทลําดับชั้น คุณสามารถระบุคอลัมน์แกนได้หลายคอลัมน์:

let
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"ProductChart", "Sales", "Chart",
                [
                    ChartType = "Column",
                    ChartTitle = "Product Sales by Category",
                    DataSeries = [
                        AxisColumns = {"Category", "Product"},
                        ValueColumns = {"Revenue"},
                        PrimaryAxisColumn = "Product"  // Use Product as the label
                    ]
                ],
                SalesData
            }
        }
    )
in
    excelDocument

การใช้ Office.InferChartPropertiesGenerator

ฟังก์ชันนี้จะ Office.InferChartPropertiesGenerator สร้างกลไกการอนุมานที่กําหนดคุณสมบัติของแผนภูมิโดยอัตโนมัติตามข้อมูลของคุณ ความสามารถนี้มีประโยชน์เมื่อคุณต้องการค่าเริ่มต้นที่เหมาะสมโดยไม่ต้องระบุรายละเอียดการกําหนดค่าแผนภูมิทั้งหมด

การใช้งานพื้นฐาน

let
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {null, null, "Workbook",
                [ChartInferenceFunction = Office.InferChartPropertiesGenerator()],
                null
            },
            // Charts without explicit ChartType will use inference
            {"Chart1", "AutoChart", "Chart", [], SalesData}
        }
    )
in
    excelDocument

ตัวเลือกการอนุมาน

Office.InferChartPropertiesGenerator ยอมรับเรกคอร์ดที่ไม่บังคับด้วยตัวเลือกต่อไปนี้:

ตัวอย่างพร้อมตัวเลือกการอนุมาน

let
    chartInference = Office.InferChartPropertiesGenerator([
        Allow3DCharts = true,
        PreferMultilevelChartInference = true
    ]),

    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {null, null, "Workbook",
                [ChartInferenceFunction = chartInference],
                null
            },
            {"Sales", "SalesChart", "Chart", 
                [DataSeries = [AxisColumns = {"Region"}, ValueColumns = {"Revenue"}]],
                SalesData
            }
        }
    )
in
    excelDocument

การอนุมานทํางานอย่างไร

กลไกจัดการการอนุมานจะส่งคืนเรกคอร์ดที่มีสามฟังก์ชัน:

• ChartType: วิเคราะห์ Schema ข้อมูลและกําหนดชนิดแผนภูมิที่เหมาะสมที่สุดโดยอิงจาก:

  • จํานวนชุดข้อมูล
  • จํานวนหมวดหมู่
  • คอลัมน์แกนเป็นวันที่และเวลา ตัวเลข หรือหมวดหมู่
  • จำนวนแถว

• ChartTitle: สร้างชื่อที่สื่อความหมายตามประเภทแผนภูมิและชื่อคอลัมน์

• DataSeries: อนุมานแกนและคอลัมน์ค่าจากสคีมาตาราง:

  • คอลัมน์วันที่และเวลาเป็นคอลัมน์แกน
  • คอลัมน์ตามหมวดหมู่ (ข้อความ) จะได้รับการพิจารณาสําหรับคอลัมน์แกน
  • คอลัมน์ตัวเลขจะกลายเป็นคอลัมน์ค่า

ตรรกะการเลือกชนิดแผนภูมิ

กลไกจัดการการอนุมานจะเลือกชนิดแผนภูมิตามลักษณะของข้อมูล:

ฟังก์ชันการอนุมานแบบกําหนดเอง

คุณสามารถจัดเตรียมฟังก์ชันการอนุมานของคุณเองที่เป็นไปตามอินเทอร์เฟซเดียวกัน:

let
    customInference = () as record => [
        ChartType = (partName, columns, dataSeries, rowCount) => 
            if rowCount < 10 then "Pie" else "Column",
        
        ChartTitle = (partName, chartType, dataSeries) => 
            partName & " Chart",
        
        DataSeries = (partName, columns) => [
            AxisColumns = {"Category"},
            ValueColumns = {"Revenue"}
        ]
    ],

    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {null, null, "Workbook",
                [ChartInferenceFunction = customInference],
                null
            },
            {"Data", "CustomChart", "Chart", [], SalesData}
        }
    )
in
    excelDocument

สถานการณ์ขั้นสูง

ส่วนนี้ครอบคลุมวิธีการแบบเป็นโปรแกรมสําหรับการสร้างตารางการนําทางแบบไดนามิก

การแบ่งพาร์ติชันข้อมูลออกเป็นหลายแผ่น

คุณสามารถใช้ฟังก์ชันรายการและตารางของ Power Query เพื่อสร้างแผ่นงานแบบไดนามิกตามค่าข้อมูล

การใช้ Table.Group

จัดกลุ่มข้อมูลตามคอลัมน์และสร้างแผ่นงานสําหรับแต่ละกลุ่ม:

let
    groupedData = Table.Group(
        SalesData, 
        {"Region"}, 
        {{"RegionData", each _, type table}}
    ),

    excelDocument = Table.FromRecords(
        List.Transform(
            Table.ToRecords(groupedData),
            (row) => [
                Sheet = row[Region],
                PartType = "SheetData",
                Data = row[RegionData]
            ]
        )
    )
in
    excelDocument

การใช้ Table.Partition

แบ่งตารางออกเป็นกลุ่มตามฟังก์ชันแฮชแบบกําหนดเอง ตัวอย่างนี้แบ่งข้อมูลการขายออกเป็นกลุ่มรายได้:

let
    Low_Revenue_Group_Index = 0,
    Medium_Revenue_Group_Index = 1,
    High_Revenue_Group_Index = 2,
    NumberOfPartitions = 3,

    RevenueRangeHash = (revenue as number) as number => 
        if revenue >= 40000 then High_Revenue_Group_Index
        else if revenue >= 25000 then Medium_Revenue_Group_Index
        else Low_Revenue_Group_Index,
    
    PartitionedList = Table.Partition(
        SalesData, 
        "Revenue", 
        NumberOfPartitions, 
        RevenueRangeHash 
    ),
    
    BucketNames = {
        "Low Revenue (< $25K)",      
        "Medium Revenue ($25K - $40K)", 
        "High Revenue (> $40K)"    
    },
    
    NamedPartitions = Table.FromColumns(
        {BucketNames, PartitionedList},
        type table [Sheet = text, Data = table]
    ),

    excelDocument = Table.AddColumn(NamedPartitions, "PartType", each "SheetData", type text)
in
    excelDocument

ฟังก์ชั่นการแบ่งพาร์ติชันแบบกําหนดเอง

สําหรับการควบคุมเพิ่มเติม ให้ใช้ฟังก์ชันการแบ่งพาร์ติชันแบบกําหนดเอง:

let
    partitionByColumn = (table as table, columnName as text, maxPartitions as number) as table =>
        let
            distinctValues = List.Distinct(Table.Column(table, columnName)),
            limitedValues = List.FirstN(distinctValues, maxPartitions),
            partitionedTable = Table.FromRecords(
                List.Transform(
                    limitedValues, 
                    (value) => [
                        Sheet = columnName & " - " & Text.From(value),
                        PartType = "SheetData",
                        Data = Table.SelectRows(table, each Record.Field(_, columnName) = value)
                    ]
                )
            )
        in
            partitionedTable,

    // Create sheets for each unique Region value (up to 10)
    excelDocument = partitionByColumn(SalesData, "Region", 10)
in
    excelDocument

แผนภูมิแบบไดนามิกสําหรับข้อมูลที่แบ่งพาร์ติชัน

รวมการแบ่งพาร์ติชันกับแผนภูมิเพื่อสร้างการแสดงภาพสําหรับแต่ละกลุ่ม:

let
    buildReference = (name as text) => #table({}, {}) meta [Name = name],

    createPartitionWithChart = (table as table, columnName as text) as table =>
        let
            distinctValues = List.Distinct(Table.Column(table, columnName)),
            partitionRows = List.Transform(
                distinctValues,
                (value) => 
                    let
                        partitionData = Table.SelectRows(table, each Record.Field(_, columnName) = value),
                        sheetName = Text.From(value),
                        dataName = "Data_" & sheetName,
                        chartName = "Chart_" & sheetName
                    in
                        {
                            {sheetName, dataName, "SheetData", [], partitionData},
                            {sheetName & " Chart", chartName, "Chart", 
                                [ChartType = "Column", ChartTitle = sheetName & " Analysis"],
                                buildReference(dataName)}
                        }
            ),
            workbookRow = {{null, null, "Workbook", [ChartInferenceFunction = Office.InferChartPropertiesGenerator()], null}}
        in
            #table(
                type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
                List.Combine({workbookRow} & partitionRows)
            ),

    excelDocument = createPartitionWithChart(SalesData, "Region")
in
    excelDocument

รูปแบบทั่วไป

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

รูปแบบ: การส่งออกข้อมูลอย่างง่าย

ส่งออกตารางเดียวไปยัง Excel ด้วยการกําหนดค่าขั้นต่ํา:

let
    excelDocument = #table(
        type table [PartType = nullable text, Data = any],
        {{"SheetData", YourDataTable}}
    )
in
    excelDocument

รูปแบบ: แผ่นงานหลายแผ่นจากรายการ

สร้างแผ่นงานหนึ่งแผ่นต่อรายการในรายการ:

let
    // Assume you have a list of {Name, Table} pairs
    dataSets = {
        {"Sales", SalesTable},
        {"Inventory", InventoryTable},
        {"Customers", CustomersTable}
    },
    
    excelDocument = #table(
        type table [Sheet = text, PartType = text, Data = table],
        List.Transform(dataSets, each {_{0}, "SheetData", _{1}})
    )
in
    excelDocument

รูปแบบ: ข้อมูลที่มีแผนภูมิบนแผ่นงานเดียวกัน

สร้างชีตที่มีทั้งข้อมูลและการแสดงภาพ:

let
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {null, null, "Workbook", [ChartInferenceFunction = Office.InferChartPropertiesGenerator()], null},
            {"Report", "SalesChart", "Chart", 
                [ChartType = "Column", Bounds = "F2:M18"], 
                YourDataTable
            }
        }
    )
in
    excelDocument

รูปแบบ: แดชบอร์ดที่มีแผนภูมิหลายแผนภูมิแบ่งปันข้อมูล

สร้างชีตแดชบอร์ดที่มีแผนภูมิหลายแผนภูมิที่อ้างอิงแหล่งข้อมูลเดียว:

let
    ref = (name as text) => #table({}, {}) meta [Name = name],
    
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"Data", "SourceData", "SheetData", [], YourDataTable},
            {"Dashboard", "Chart1", "Chart", 
                [ChartType = "Column", Bounds = "A1:H16", 
                 DataSeries = [AxisColumns = {"Category"}, ValueColumns = {"Value1"}]], 
                ref("SourceData")},
            {"Dashboard", "Chart2", "Chart", 
                [ChartType = "Line", Bounds = "J1:Q16", 
                 DataSeries = [AxisColumns = {"Category"}, ValueColumns = {"Value2"}]], 
                ref("SourceData")}
        }
    )
in
    excelDocument

รูปแบบ: โต๊ะเคียงข้างกัน

วางตารางสองตารางติดกันบนแผ่นงานเดียวกัน:

let
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"Report", "MainData", "Table", 
                [StartCell = "A1", TableStyle = "TableStyleMedium2"], 
                MainTable},
            {"Report", "Summary", "Table", 
                [StartCell = "H1", TableStyle = "TableStyleLight15"], 
                SummaryTable}
        }
    )
in
    excelDocument

รูปแบบ: โต๊ะเรียงซ้อนพร้อมการวางตําแหน่งอัตโนมัติ

วางซ้อนหลายตารางในแนวตั้งด้วยระยะห่างอัตโนมัติ:

let
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"Report", "Section1", "Table", [], Table1},
            {"Report", "Section2", "Table", [AutoPositionRowOffset = 2], Table2},
            {"Report", "Section3", "Table", [AutoPositionRowOffset = 2], Table3}
        }
    )
in
    excelDocument

รูปแบบ: ล้างรายงานโดยไม่มีเส้นตาราง

สร้างรายงานที่สวยงามด้วยเส้นตารางที่ซ่อนอยู่:

let
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"Report", "Data", "Table", 
                [StartCell = "B2", ShowGridlines = false, TableStyle = "TableStyleMedium9"], 
                YourDataTable}
        }
    )
in
    excelDocument

หมายเหตุและข้อจำกัด

ขีดจํากัดของ Excel

Excel มีข้อจํากัดโดยธรรมชาติที่ส่งผลต่อการสร้างเอกสาร:

• แถวสูงสุดต่อแผ่นงาน: 1,048,576 แถว
• คอลัมน์สูงสุดต่อแผ่นงาน: 16,384 คอลัมน์
• อักขระสูงสุดต่อเซลล์: 32,767 อักขระ
• ความยาวสูงสุดของชื่อแผ่นงาน: 31 อักขระ
• ชื่อแผ่นงานไม่สามารถมี: \ / ? * [ ] หรือขึ้นต้น/ลงท้ายด้วย '

เมื่อข้อมูลของคุณเกินขีดจํากัดเหล่านี้ ให้พิจารณาแบ่งพาร์ติชันข้อมูลในหลายแผ่นงาน

การจัดการข้อผิดพลาด

ข้อผิดพลาดระดับเซลล์ในข้อมูลของคุณทําให้การสร้างเอกสารล้มเหลว ตัวเชื่อมต่อจะตรวจสอบความถูกต้องของข้อมูลขณะประมวลผลแต่ละแถว และพบค่าความผิดพลาด (เช่น การหารด้วยศูนย์หรือข้อผิดพลาดในการแปลงชนิด) จะทําให้เกิดข้อยกเว้น

เพื่อป้องกันความล้มเหลว:

• ใช้ try...otherwise นิพจน์เพื่อจัดการกับข้อผิดพลาดที่อาจเกิดขึ้นก่อนส่งออก
• แทนที่ค่าความผิดพลาดด้วยค่าเริ่มต้นหรือค่า Null ที่เหมาะสม
• ตรวจสอบคุณภาพข้อมูลก่อนสร้างตารางการนําทางของคุณ

let
    RawData = #table(
        type table [Product = text, Amount = text],
        {
            {"Laptop", "1250.50"},
            {"Phone", "N/A"},
            {"Tablet", "850.00"}
        }
    ),
    
    cleanData = Table.TransformColumns(
        RawData,
        {{"Amount", each try Number.From(_) otherwise null, type nullable number}}
    ),
    
    excelDocument = #table(
        type table [PartType = nullable text, Data = any],
        {{"SheetData", cleanData}}
    )
in
    excelDocument

การจัดการชื่อ

ชื่อแผ่นงานและชิ้นส่วนจะถูกฆ่าเชื้อโดยอัตโนมัติเพื่อให้สอดคล้องกับกฎการตั้งชื่อ Excel อักขระที่ไม่ถูกต้องจะถูกลบออก และชื่อจะถูกตัดทอนเป็น 31 อักขระ ถ้า StrictNameHandling เปิดใช้งานในคุณสมบัติ เวิร์กบุ๊ก ข้อผิดพลาดจะถูกส่งแทนการฆ่าเชื้ออัตโนมัติ

ชื่อแผ่นงานที่ซ้ํากันจะถูกทําให้ไม่ซ้ํากันโดยอัตโนมัติโดยการต่อท้ายตัวเลข

แก้ไข ปัญหา

ส่วนนี้ครอบคลุมข้อผิดพลาดทั่วไปที่คุณอาจพบเมื่อสร้างตารางการนําทางและวิธีแก้ไข

การอ้างอิงข้อผิดพลาด

ปัญหาทั่วไป

ปัญหาโครงสร้างตารางการนําทาง

ปัญหา: การสร้างเอกสารล้มเหลวโดยมีข้อผิดพลาด "ค่าหายไป" หรือ schema

สาเหตุ: ตารางการนําทางไม่มีคอลัมน์ที่จําเป็นหรือมีชนิดคอลัมน์ที่ไม่ถูกต้อง

วิธีแก้ไข: ตรวจสอบว่าตารางการนําทางของคุณมีคอลัมน์ที่ถูกต้องและมีชนิดที่เหมาะสม:

type table [
    Sheet = nullable text,        // Optional: worksheet name
    Name = nullable text,         // Unique identifier for the part
    PartType = text,              // Required: "Workbook", "SheetData", "Table", "Range", "Text", or "Chart"
    Properties = nullable record, // Configuration options
    Data = any                    // Table, table reference, or null
]

การอนุมานแผนภูมิไม่ทํางาน

ปัญหา: แผนภูมิล้มเหลวในการสร้างโดยมีข้อผิดพลาด "ไม่มีคอลัมน์แกน/ค่าที่ให้ไว้"

สาเหตุ: ไม่มี ChartInferenceFunction การกําหนดค่าและการแม็ปคอลัมน์ที่ชัดเจนหายไป

วิธีการแก้: เพิ่มส่วนสมุดงานที่มีฟังก์ชันการอนุมานเป็นแถวแรก:

{null, null, "Workbook", [ChartInferenceFunction = Office.InferChartPropertiesGenerator()], null}

หรือระบุ DataSeries อย่างชัดเจนสําหรับแต่ละแผนภูมิ:

[DataSeries = [AxisColumns = {"Category"}, ValueColumns = {"Revenue", "Profit"}]]

การอ้างอิงตารางไม่สามารถแก้ไขได้

ปัญหา: ข้อผิดพลาด "ไม่พบตารางที่อ้างอิง" เมื่อใช้การอ้างอิงตาราง

สาเหตุ: Name ข้อมูลเมตาในการอ้างอิงไม่ตรงกับส่วนข้อมูลใดๆ

วิธีแก้ไข: ตรวจสอบให้แน่ใจว่าชื่อเมตาดาต้าตรงกันทุกประการ:

let
    // Data part with name "SalesData"
    dataRow = {"Sales", "SalesData", "SheetData", [], actualTable},
    
    // Chart referencing the data - name must match
    chartRow = {"Chart", "SalesChart", "Chart", [], #table({}, {}) meta [Name = "SalesData"]}
in
    ...

ข้อผิดพลาดของชนิดข้อมูลในเซลล์

ปัญหา: การสร้างเอกสารล้มเหลวเมื่อประมวลผลบางแถว

สาเหตุ: ข้อมูลมีค่าความผิดพลาด (เช่น การหารด้วยศูนย์หรือความล้มเหลวในการแปลงประเภท)

วิธีแก้ไข: ล้างค่าความผิดพลาดก่อนส่งออก:

Table.TransformColumns(
    YourTable,
    {{"ColumnName", each try _ otherwise null}}
)

ค่าวันที่หรือเวลาที่ไม่รองรับ

ปัญหา: ข้อผิดพลาด "ค่าที่ระบุไม่สามารถแสดงได้ใน Excel" เมื่อส่งออกข้อมูลวันที่/เวลา

สาเหตุ: Excel ไม่สามารถแสดงวันที่ก่อนวันที่ 1 มกราคม 1900 หรือช่วงเวลาที่อยู่นอกช่วงที่ได้รับการสนับสนุน

วิธีแก้ไข: กรองค่าที่ไม่รองรับออกหรือแปลง:

Table.SelectRows(YourTable, each [DateColumn] >= #date(1900, 1, 1))

ข้อผิดพลาดในการผสมข้อมูลแผ่นและการผสมชิ้นส่วนที่วางตําแหน่ง

ปัญหา: รายงานข้อผิดพลาดที่ SheetData ไม่สามารถรวมกับชิ้นส่วนอื่นในแผ่นเดียวกันได้

สาเหตุ: คุณไม่สามารถรวม SheetData กับ Table, Range, หรือ Text ชิ้นส่วนบนแผ่นเดียวกันได้

วิธีแก้: ใช้ SheetData แผ่นเดียวหรือใช้ชิ้นส่วนที่จัดวางร่วมกัน:

// Option 1: Use SheetData alone
{"Sheet1", "Data", "SheetData", [], myTable}

// Option 2: Use Table, Range, or Text parts for multiple regions
{"Sheet1", "Title", "Text", [StartCell = "A1:J1"], "Sales report"},
{"Sheet1", "Table1", "Table", [StartCell = "A3"], firstTable},
{"Sheet1", "Table2", "Table", [StartCell = "F3"], secondTable}

ความขัดแย้งในการวางตําแหน่งอัตโนมัติ

ปัญหา: ข้อผิดพลาด "ส่วนระบุ AutoPositionColumnOffset หรือ AutoPositionRowOffset แต่มี StartCell ที่ชัดเจน"

สาเหตุ: คุณไม่สามารถใช้ออฟเซ็ตตําแหน่งอัตโนมัติเมื่อ StartCell มีการตั้งค่าไว้อย่างชัดเจน

วิธีแก้ไข: ลบ StartCell เพื่อใช้การวางตําแหน่งอัตโนมัติ หรือลบคุณสมบัติออฟเซ็ต:

// Auto positioning with custom offsets (no StartCell)
{"Sheet1", "Table1", "Table", [AutoPositionColumnOffset = 2, AutoPositionRowOffset = 1], myTable}

// Or explicit positioning (no offset properties)
{"Sheet1", "Table1", "Table", [StartCell = "C2"], myTable}

ชื่อแผ่นงานไม่ถูกต้อง

ปัญหา: เกิดข้อผิดพลาดในการกล่าวถึงชื่อคอนเทนเนอร์ที่ไม่ถูกต้องหรือการฆ่าเชื้อชื่ออัตโนมัติ

สาเหตุ: ชื่อแผ่นงานมีอักขระที่ไม่ถูกต้อง (\ / ? * [ ]) หรือเกิน 31 อักขระ

วิธีแก้ไข: ฆ่าเชื้อชื่อแผ่นงานของคุณล่วงหน้า:

sanitizeName = (name as text) as text =>
    let
        cleaned = Text.Replace(Text.Replace(name, "/", "-"), "\", "-"),
        truncated = Text.Start(cleaned, 31)
    in
        truncated

เนื้อหาที่เกี่ยวข้อง

• ข้อมูลจําเพาะของภาษา Power Query M
• การอ้างอิงฟังก์ชันตาราง
• กระแสข้อมูล Power Query