Forum Discussion

rdraytonNBRS's avatar
rdraytonNBRS
Frequent Visitor
3 years ago

Removing Summary Totals from a Query

Afternoon All,   I have a query of a data table that contains both the individual line item values, along with the summary values of the lower level line items in the one table. See the screenshot ...
Need Help
bolfri's avatar
bolfri
Icon for Solution Sage rankSolution Sage
3 years ago

Based on Sample Data that you've provided (SampleData Table).
Step 1. Create MaxWBSNesting table

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZbRboMgFIZfhZhd2gVQYL12y27W2KS9WZpesJUYN4sN2j1Pn6VPNmzaTJh1oF0MEQX5Pw78B1erAEMM7yEKwmCuyg/xXgO7LvdF8et2PNwdD10XQHEIoR4S6l5NQcE6vCaz5NUnSNVGqLFKzeeRu9I8AUlZqk0ueZ2XcqAo1iUeIzoTos5lVgEuN1eArpKcaNgPTKQL+S+Yxf5tcm63m5wBY13oGMCkrGrwVNX5ljcgA2PEbovwKKo8k5d3ovKNyMMFB5s42Kq7egNMGQ5RpwE7FNoTBhOQpD5KiLaU0MkPfkrPQgrFCx1kWSuuO8645JnYClm7O9JAemhbM/LE0e2Z4tvxFKRNQQdSLPa7XaluEggEfRlmYKl4Lg2X/ZkaKTRTI4pHybaewIv4EkX7jHCjaPyOiDdF6hN8oC1gzhs7+sDH1hdzk5AYR57j5CJ/KWKfrlM3qdgnheB27JpB4949kxK9SBF4FVyBs08GxNCWZP6SN8tcwP6d6I1yN8yYvAUYNQFIb9a8AjAoXSDLNrTXNin1X2kGTbdQR7cwny0c2VuY6Wy7/gY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Contract ID" = _t, #"Contract Name" = _t, WBS.1 = _t, WBS.2 = _t, WBS.3 = _t, WBS.4 = _t, #" Fee " = _t, #"WBS Nesting" = _t, #"Sort Order" = _t]),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"WBS.1", "WBS Nesting"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"WBS.1", type text}, {"WBS Nesting", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"WBS.1"}, {{"MaxNesting", each List.Max([WBS Nesting]), type nullable number}})
in
    #"Grouped Rows"

 

 

Step 2. Merge this table to your source on WBS.1 and Expand MaxNesting Coulmn to your source.

Step 3. Create custom column

if [Contract Name] = [WBS.1] or [WBS Nesting] <> [MaxNesting] then "REMOVE" else "KEEP"

 

Final result:

Filter out the REMOVE rows 🙂

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZbRboMgFIZfhZhd2gVQYL12y27W2KS9WZpesJUYN4sN2j1Pn6VPNmzaTJh1oF0MEQX5Pw78B1erAEMM7yEKwmCuyg/xXgO7LvdF8et2PNwdD10XQHEIoR4S6l5NQcE6vCaz5NUnSNVGqLFKzeeRu9I8AUlZqk0ueZ2XcqAo1iUeIzoTos5lVgEuN1eArpKcaNgPTKQL+S+Yxf5tcm63m5wBY13oGMCkrGrwVNX5ljcgA2PEbovwKKo8k5d3ovKNyMMFB5s42Kq7egNMGQ5RpwE7FNoTBhOQpD5KiLaU0MkPfkrPQgrFCx1kWSuuO8645JnYClm7O9JAemhbM/LE0e2Z4tvxFKRNQQdSLPa7XaluEggEfRlmYKl4Lg2X/ZkaKTRTI4pHybaewIv4EkX7jHCjaPyOiDdF6hN8oC1gzhs7+sDH1hdzk5AYR57j5CJ/KWKfrlM3qdgnheB27JpB4949kxK9SBF4FVyBs08GxNCWZP6SN8tcwP6d6I1yN8yYvAUYNQFIb9a8AjAoXSDLNrTXNin1X2kGTbdQR7cwny0c2VuY6Wy7/gY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Contract ID" = _t, #"Contract Name" = _t, WBS.1 = _t, WBS.2 = _t, WBS.3 = _t, WBS.4 = _t, #" Fee " = _t, #"WBS Nesting" = _t, #"Sort Order" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract ID", type date}, {"Contract Name", type text}, {"WBS.1", type text}, {"WBS.2", type text}, {"WBS.3", type text}, {"WBS.4", type text}, {" Fee ", type text}, {"WBS Nesting", Int64.Type}, {"Sort Order", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"WBS.1"}, MAXWBSNesting, {"WBS.1"}, "MAXWBSNesting", JoinKind.LeftOuter),
    #"Expanded MAXWBSNesting" = Table.ExpandTableColumn(#"Merged Queries", "MAXWBSNesting", {"MaxNesting"}, {"MaxNesting"}),
    #"Added Custom" = Table.AddColumn(#"Expanded MAXWBSNesting", "Keep or Remove", each if [Contract Name] = [WBS.1] or [WBS Nesting] <> [MaxNesting] then "REMOVE" else "KEEP")
in
    #"Added Custom"