excel - Aggregation/Summation of text and numeric fields

Question

Raw data

SiteName-----Agency------Staff Numbers

  Site1-----------A1------------10  

  Site1-----------A1------------12

  Site1-----------A1------------11

  Site1-----------A2-------------5

Wondering how I can get the following in my pivot report;

Site1-------------A1/A2---------33/5

a summation/aggregation of both the "Agency" and "Staff Numbers";

Note that I have successfully aggregated on the "Agency" field (text) using concatenatex with a "/" delimiter, but when it comes to the "Staff Numbers" field (numeric) I am not getting any summation of the staff numbers.

I get;

Site1--------A1/A2----------10/12/11/5--------> this is undesirable

I want;

Site1--------A1/A2----------33/5---------------> this is desirable

标签： excel

Answer 1

CONCATANATEX is a DAX function, isn't it?

In any event, you can do this in Power Query with a nested Table.Group function.

To use Power Query

• Select some cell in your Data Table
• Data => Get&Transform => from Table/Range
• When the PQ Editor opens: Home => Advanced Editor
• Make note of the Table Name in Line 2
• Paste the M Code below in place of what you see
• Change the Table name in line 2 back to what was generated originally.
• Read the comments and explore the Applied Steps to understand the algorithm

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SiteName", type text}, {"Agency", type text}, {"Staff Numbers", Int64.Type}}),
    
//Group by SiteName
    #"Grouped Rows" = Table.Group(#"Changed Type", {"SiteName"}, {
        
//then subGroup by Agency
        {"Agency", each Table.Group(_,"Agency", {
            {"Staff Numbers", each List.Sum(_[Staff Numbers])}   
        })}
        }),

//Combine the agencies/staff
    agencies = Table.AddColumn(#"Grouped Rows", "Agencies", each Text.Combine([Agency][Agency],"/")),
    staff = Table.AddColumn(agencies, "Staff Numbers", each 
        Text.Combine(
            List.Transform([Agency][Staff Numbers], each Text.From(_)),
        "/")),

//remove unneeded column
    #"Removed Columns" = Table.RemoveColumns(staff,{"Agency"})
in
    #"Removed Columns"