How to groupy by one column a source that's been grouped by two columns

[riwaomv]

Table A below is an example of my datasource (actually I'm retrieving it from Snowflake in Power BI). For every combination of Category + Name, I would like to take the max value of the distance (because I know that distance will be the same for each catgeory+name combination). This means for example, I will take the distance in line number 4 and ignore line number 5 completely.

Then as you can see in table B (which is the result that I'm trying to create in Power BI), I want to rollup this value to be on the category level (this means Sum of all the values that I got when I had the data grouped by category + name).

How can I achieve this in Power BI? I've explored rollupgroup, calculate(max,,allexcept...) and many other options but haven't figured it out yet.

[dufoq3]

Hi @riwaomv,

for future add sample data as tables so we can copy it.

You can achieve this also in Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXI0BBIgbKQUqwMVMgJzdZRMcAk5AVlOIE3GQGyGVwiiSUfJAizkDGQ5g1SZgmw1UIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Name = _t, Values = _t, Distance = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Values", type number}, {"Distance", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category", "Name"}, {{"Max Distance", each Table.FromRecords({Table.Max(_, "Distance")}), type table}}),
    #"Expanded Max Distance" = Table.ExpandTableColumn(#"Grouped Rows", "Max Distance", {"Values", "Distance"}, {"Values", "Distance"}),
    #"Grouped Rows1" = Table.Group(#"Expanded Max Distance", {"Category"}, {{"Values", each List.Sum([Values]), type text}, {"Distance", each List.Sum([Distance]), type text}})
in
    #"Grouped Rows1"

[riwaomv]

Thank you for your quick reply. I saw the video that you sent me and I tried your query but I'm having trouble linking it to my source of data. Since I'm using a Snowflake connection, it's not identifiying the columns. This is how I added the logic. Note that I also tried to use in #"Grouped Rows1" as you did in your answer instead of in RPT_Test_view.

= let
Source = Snowflake.Databases("snowflake link","warehouse name"),
DATA_LAKE_DEV_Database = Source{[Name="DATA_LAKE_DEV",Kind="Database"]}[Data],
BI_Schema = DATA_LAKE_DEV_Database{[Name="BI",Kind="Schema"]}[Data],
RPT_Test_View = BI_Schema{[Name="RPT_Test",Kind="View"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Values", type number}, {"Distance", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category", "Name"}, {{"Max Distance", each Table.FromRecords({Table.Max(_, "Distance")}), type table}}),
    #"Expanded Max Distance" = Table.ExpandTableColumn(#"Grouped Rows", "Max Distance", {"Values", "Distance"}, {"Values", "Distance"}),
    #"Grouped Rows1" = Table.Group(#"Expanded Max Distance", {"Category"}, {{"Values", each List.Sum([Values]), type text}, {"Distance", each List.Sum([Distance]), type text}})
in
    RPT_Test_View

in
RPT_Test_View

[dufoq3]

@riwaomv, I've just updated my signature. Check this link to see how to use my query.