Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Power Query - Sum by Category WITHOUT Grouping

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

Need Help
Show and Tell
  • Nolock's avatar
    Nolock
    7 years ago

    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 :smileyhappy:

1 Reply

  • Nolock's avatar
    Nolock
    Resident Rockstar
    7 years ago

    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 :smileyhappy: