March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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!!
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
Proud to be a Super User!
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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
133 | |
91 | |
88 | |
64 | |
58 |
User | Count |
---|---|
201 | |
137 | |
107 | |
73 | |
68 |