Issue with grouping percentages based on years

Hi Tasha_DGS 

What exactly is the final desired result?  Do you want to keep rows that have a 0 % value?

In other words do you want this

or this

If you want the first one, right click the Item Code column header then select Unpivot Other Columns. You can the delete the Attribute column and filter out 0 values from the Value column to leave you with the final result.

Here's the query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvKPcHIMdlXSUcorzckBUgZ6BoYwXqwOWIGPpwuKvAGQAsu5RoQEOQb7Ovr4YJf3cXfVBeo3RshCFBjjkEXW6+vqQpIsRIEZsqwJmqwhDklkg4N9fUCyhtidjCmLRa8RsbIQBSZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item Code" = _t, #"21/22 % Total Weight" = _t, #"22/23 % Total Weight" = _t, #"23/24 % Total Weight" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item Code", type text}, {"21/22 % Total Weight", Percentage.Type}, {"22/23 % Total Weight", Percentage.Type}, {"23/24 % Total Weight", Percentage.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Item Code"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> 0))
in
    #"Filtered Rows"

If you want to keep items with a 0% value, Unpivot the other columns as in the first example, but then Group the Item Code and select Max as the aggregation

Here's the query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvKPcHIMdlXSUcorzckBUgZ6BoYwXqwOWIGPpwuKvAGQAsu5RoQEOQb7Ovr4YJf3cXfVBeo3RshCFBjjkEXW6+vqQpIsRIEZsqwJmqwhDklkg4N9fUCyhtidjCmLRa8RsbIQBSZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item Code" = _t, #"21/22 % Total Weight" = _t, #"22/23 % Total Weight" = _t, #"23/24 % Total Weight" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item Code", type text}, {"21/22 % Total Weight", Percentage.Type}, {"22/23 % Total Weight", Percentage.Type}, {"23/24 % Total Weight", Percentage.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Item Code"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Item Code"}, {{"% of Total Weight", each List.Max([Value]), type number}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"% of Total Weight", Percentage.Type}})
in
    #"Changed Type1"

Both examples are in this PBIX file

Regards

Phil