Removing Summary Totals from a Query

Question

Afternoon All,&nbsp;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 below.&nbsp;Line 1 is a summary of all Fee amounts in WBS1=21064Live 4 is a summary of all Fee amounts in 21064 &amp; WBS2=10 etc. etc.&nbsp;Ideally I would like to remove the summary rows and only leave the lowest level Fee amounts so that BI can do the summarising itself in the visuals etc.&nbsp;The data has some lines where the lowest level is at WBS1, others with WBS1 and WBS2, and others where there is all three levels.&nbsp;Any help on how to cleanup this data to the lowest available level of data would be appreciated.&nbsp;&nbsp;Cheers!

bolfri · Answer

Based on Sample Data that you've provided (SampleData Table).Step 1. Create&nbsp;MaxWBSNesting table&nbsp;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"&nbsp;&nbsp;Step 2. Merge this table to your source on WBS.1 and Expand MaxNesting Coulmn to your source.Step 3. Create custom columnif [Contract Name] = [WBS.1] or [WBS Nesting] &lt;&gt; [MaxNesting] then "REMOVE" else "KEEP"&nbsp;Final result:Filter out the REMOVE rows 🙂&nbsp;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] &lt;&gt; [MaxNesting] then "REMOVE" else "KEEP")
in
    #"Added Custom"

anonymous · Answer

Hi&nbsp;rdraytonNBRS&nbsp;,
&nbsp;
You may refer to my M code to learn more details about how to remove summarize.
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBRCgAhCETv4ncfjmbsniW6/zVWra/FICacN6I4JzGokYBH93+/14yZVvtB9xIXCIGgLqZ241HrHUNchlSTN0fKUwUkAiLpVAM2D6d7/y0Q63PZrwejvkw/eCRdHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ContractNumber = _t, WBS1 = _t, WBS2 = _t, WBS3 = _t, Fee = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ContractNumber", type text}, {"WBS1", Int64.Type}, {"WBS2", Int64.Type}, {"WBS3", Int64.Type}, {"Fee", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Fee", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ContractNumber", "WBS1", "WBS2"}, {{"Count", each _, type table [ContractNumber=nullable text, WBS1=nullable number, WBS2=nullable number, WBS3=nullable number, Fee=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"WBS3", "Fee", "Index"}, {"Custom.WBS3", "Custom.Fee", "Custom.Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom.WBS3", "WBS3"}, {"Custom.Fee", "Fee"}, {"Custom.Index", "Index"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "0 remove and 1 keep", each let _Max =
List.Max(
let 
_ContractNumber = [ContractNumber],
_WBS1 = [WBS1],
_WBS2 = [WBS2]
in
Table.SelectRows(#"Renamed Columns",each _ContractNumber = [ContractNumber] and _WBS1 = [WBS1] and _WBS2 = [WBS2])[Index]
,
[Index])
in
if [WBS2] = null then 0 
else if _Max = 1 then 1 else if [Index] = 1 then 0 else 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([0 remove and 1 keep] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count", "Index", "0 remove and 1 keep"})
in
    #"Removed Columns"
Result is as below.

&nbsp;
Best Regards,Rico Zhou
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;

rdraytonnbrs · Answer

Thanks for the assistance. I think this solution will only work where I only have one WBS1 value (as in my sample). I actually need a solution that will work if I have MULTIPLE WBS1 values in my table. Is there a simple addition here?

See a larger data sample below.