关于Power Query GUI实现嵌套表及等效Group By代码功能的技术咨询

Hi DullesTower, let's break this down step by step to help you get the exact results you're looking for.

1. GUI Equivalent for Nested Table Grouping #

Yes, you can create nested tables using Power Query's GUI—you just need to use the right options in the Group By dialog:

• Open the Group By dialog (under the Transform tab)
• First, select your top-level grouping column (e.g., Site)
• For the aggregation, choose All Rows from the dropdown menu. This will create a nested table containing all rows for each Site value.
• To add a second level of nesting (e.g., Agency inside Site), you'll need to:
  1. Click into the nested table column created in the first step
  2. Open the Group By dialog again, this time grouping by Agency and selecting All Rows again
  3. Repeat this process if you want to add a third level (like Division inside Agency)

The reason your initial GUI attempt might have differed from the code is that the GUI generates sequential Table.Group calls (one per level), whereas handwritten code can combine multiple levels in a single concise statement. But the end nested table structure can be identical if you follow these steps.

2. Correct Nested Table.Group Syntax for Your Target Outcome #

Based on your description of wanting combinations like Site→Agency→Division, Site→Agency, Site→Division, it sounds like you want a structured summary that includes multiple levels of aggregation. Here's how to write a Table.Group statement that matches your target outcome:

let
    Source = YourDataSource, // Replace with your actual data source
    // Group by Site, then add nested aggregations for different level combinations
    GroupedBySite = Table.Group(Source, {"Site"}, {
        // Nested hierarchy: Site → Agency → Division (with summed values)
        {"AgencyDivisionHierarchy", each Table.Group(_, {"Agency"}, {
            {"DivisionGroups", each Table.Group(_, {"Division"}, {{"Total", List.Sum([YourNumericColumn])}})}
        })},
        // Direct summary: Site → Agency (summed)
        {"AgencySummary", each Table.Group(_, {"Agency"}, {{"Total", List.Sum([YourNumericColumn])}})},
        // Direct summary: Site → Division (summed)
        {"DivisionSummary", each Table.Group(_, {"Division"}, {{"Total", List.Sum([YourNumericColumn])}})}
    })
in
    GroupedBySite

Key Notes: #

• Replace YourDataSource with your actual data source reference
• Replace YourNumericColumn with the name of the numeric field you want to sum/aggregate
• This structure creates a top-level Site grouping, with three nested columns tailored to your desired combinations:
  • AgencyDivisionHierarchy: A nested table of Agency groups, each containing Division groups with summed values
  • AgencySummary: A direct summary of each Agency under the Site
  • DivisionSummary: A direct summary of each Division under the Site

If you want aggregated values at every level of the hierarchy (e.g., total for Site, total for each Agency, total for each Division), use this adjusted version:

let
    Source = YourDataSource,
    GroupedBySite = Table.Group(Source, {"Site"}, {
        {"SiteTotal", List.Sum([YourNumericColumn])},
        {"AgencyGroups", each Table.Group(_, {"Agency"}, {
            {"AgencyTotal", List.Sum([YourNumericColumn])},
            {"DivisionGroups", each Table.Group(_, {"Division"}, {{"DivisionTotal", List.Sum([YourNumericColumn])}})}
        })}
    })
in
    GroupedBySite

内容的提问来源于stack exchange，提问作者DullesTower