Power Query - Sum by Category WITHOUT Grouping

[Anonymous]

Hi,

I have a table that has the total population figures for a specific demographic across 5 cities for 2017, 2018 and 2019. I want to work out the percentage of population each city has for each year. Here's an example of the data I'm working with:

Is there any way to do this where I can create another column with the total across the locations for each year?

Thanks,

MarkJames

[Nolock · Accepted solution]

Hi @Anonymous,

you can group the table and then join it back with the original one.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MgaSRgaG5kqxOjAhU4iQBULI2AAiZAkWcgJzDJA1goSMYUIWcCETA4TGWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Market = _t, Population = _t, Year = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Market", type text}, {"Population", Int64.Type}, {"Year", Int64.Type}}),

    // group by year and sum population
    Aggs = Table.Group(ChangedType, "Year", {"Total Population per Year", each List.Sum([Population])}),
    // rename Year to AggsYear in the table Aggs
    RenameAggsYear = Table.RenameColumns(Aggs, {{"Year", "AggsYear"}}),

    // join source table with aggregations
    JoinWithAggs = Table.Join(ChangedType, "Year", RenameAggsYear, "AggsYear", JoinKind.Inner),
    // remove column AggsYear
    RemoveAggsYearColumn = Table.RemoveColumns(JoinWithAggs, "AggsYear")
in
    RemoveAggsYearColumn

And the result:

Next time, please provide also same sample data that we can import. It simplifies the work. Thank you