Issue with grouping percentages based on years

[Tasha_DGS]

Hi All,

I have a power query that calculates the total weight of a product we have delivered in a 12 month period. 

I have calculated the total weight of all products delivered over 3 seperate financial years and the corresponding percentage of each product line for each year, however after removing the year from the query it is still seperating the items as if there was a year column. The calculation I used to get the percentage for each year is, just changing the Fiscal year for each percentage:

 "21/22 % of Total Weight", each if [Calendar.Fiscal Year] = "2022" then [Percent Of] else " ")

The image below is what I am getting, but I need to have 1 line for each item code.

Can someone let me know what I am doing wrong please!! 

[PhilipTreacy]

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

[Tasha_DGS]

Hi Phil, 

Thanks for this, it's not quite the result I'm after, probably the way I've explained it sorry!

I need to keep all three percentage columns in the table as they are needed for appotioning other values but I need to have only 1 Item code in the list.

So that it looks like the below if the item has percentages against more than 1 year, rather than a line for each year:

Hope that makes sense!