Table.Group – จัดกลุ่มและสรุปผลข้อมูล (Group By)

let
    Sales = Table.FromRecords({
        [Category = "Electronics", Amount = 500],
        [Category = "Electronics", Amount = 800],
        [Category = "Furniture", Amount = 1200],
        [Category = "Furniture", Amount = 600]
    }),
    GroupedSales = Table.Group(
        Sales,
        "Category",
        {{"TotalAmount", each List.Sum([Amount]), type number}}
    )
in
    GroupedSales

let
    Orders = Table.FromRecords({
        [CustomerID = 1, OrderValue = 100],
        [CustomerID = 1, OrderValue = 150],
        [CustomerID = 2, OrderValue = 200],
        [CustomerID = 2, OrderValue = 300],
        [CustomerID = 2, OrderValue = 100]
    }),
    CustomerSummary = Table.Group(
        Orders,
        "CustomerID",
        {
            {"TotalOrders", each Table.RowCount(_), Int64.Type},
            {"TotalValue", each List.Sum([OrderValue]), type number},
            {"AverageValue", each List.Average([OrderValue]), type number},
            {"MaxValue", each List.Max([OrderValue]), type number}
        }
    )
in
    CustomerSummary

let
    RegionalSales = Table.FromRecords({
        [Region = "North", Product = "A", Sales = 100],
        [Region = "North", Product = "A", Sales = 200],
        [Region = "North", Product = "B", Sales = 150],
        [Region = "South", Product = "A", Sales = 300],
        [Region = "South", Product = "B", Sales = 250]
    }),
    GroupedByRegionProduct = Table.Group(
        RegionalSales,
        {"Region", "Product"},
        {
            {"TotalSales", each List.Sum([Sales]), type number},
            {"TransactionCount", each List.Count([Sales]), Int64.Type}
        }
    )
in
    GroupedByRegionProduct

let
    SortedData = Table.FromRecords({
        [Status = "Active", Value = 100],
        [Status = "Active", Value = 200],
        [Status = "Active", Value = 150],
        [Status = "Inactive", Value = 50],
        [Status = "Inactive", Value = 75]
    }),
    // ข้อมูลเรียงลำดับตาม Status แล้ว (Active ติดกันหมด, Inactive ติดกันหมด)
    GroupedWithLocal = Table.Group(
        SortedData,
        "Status",
        {{"Total", each List.Sum([Value]), type number}},
        GroupKind.Local  // ระบุว่าข้อมูลเรียงลำดับแล้ว
    )
in
    GroupedWithLocal

let
    TransactionData = Table.FromRecords({
        [Date = #date(2024, 1, 15), Category = "A", Amount = 100],
        [Date = #date(2024, 1, 20), Category = "A", Amount = 200],
        [Date = #date(2024, 2, 10), Category = "B", Amount = 150],
        [Date = #date(2024, 2, 25), Category = "B", Amount = 300]
    }),
    // ขั้นตอน 1: กรองเฉพาะเดือนมกราคม
    JanuaryOnly = Table.SelectRows(
        TransactionData,
        each Date.Month([Date]) = 1
    ),
    // ขั้นตอน 2: จัดกลุ่มและสรุปผล
    GroupedByCategory = Table.Group(
        JanuaryOnly,
        "Category",
        {
            {"TotalAmount", each List.Sum([Amount]), type number},
            {"RunningTotal", each List.Accumulate([Amount], 0, (state, current) => state + current), type number}
        }
    )
in
    GroupedByCategory