Removing Summary Totals from a Query

[rdraytonNBRS]

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 below.

Line 1 is a summary of all Fee amounts in WBS1=21064

Live 4 is a summary of all Fee amounts in 21064 & WBS2=10 etc. etc.

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.

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.

Any help on how to cleanup this data to the lowest available level of data would be appreciated.

Cheers!

[bolfri]

CORRECT ANSWER

I've found an error in my previous solution when Max WBS Nesting based on WBS.1 is not same for all WBS.1 child.

That's why I've prepared a new version, but better one.

After some cleaning on the data ("null" as text replaced by null)

Step 1. Add new column ParentPath.

Step 2. Add new column CurrentPath

Step 3. Add new column isParent (checks if current path can be found in ParentPathList)

With this table you can just merge queries to receive isParent folumn and filter out Parents.

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]),
    Hierarchy = Table.SelectColumns(Source,{"Contract Name", "WBS.1", "WBS.2", "WBS.3", "WBS.4", "WBS Nesting"}),
    #"Changed Type" = Table.TransformColumnTypes(Hierarchy,{{"Contract Name", type text}, {"WBS.1", type text}, {"WBS.2", type text}, {"WBS.3", type text}, {"WBS.4", type text}, {"WBS Nesting", Int64.Type}}),
    #"Cleaning the data" = Table.ReplaceValue(#"Changed Type","null",null,Replacer.ReplaceValue,Table.ColumnNames(#"Changed Type")),
    #"Add ParentPath" = Table.AddColumn(#"Cleaning the data", "ParentPath", each
if [WBS Nesting] = 4 then
Text.Combine(List.Select({[Contract Name],[WBS.1],[WBS.2],[WBS.3]},each _<> "" and _ <> null)," & ")
else if [WBS Nesting] = 3 then
Text.Combine(List.Select({[Contract Name],[WBS.1],[WBS.2]},each _<> "" and _ <> null)," & ")
else if [WBS Nesting] = 2 then
Text.Combine(List.Select({[Contract Name],[WBS.1]},each _<> "" and _ <> null)," & ")
else [Contract Name]),
    #"Add CurrentPath" = Table.AddColumn(#"Add ParentPath", "CurrentPath", each
    if [Contract Name] = [WBS.1] then [Contract Name] else Text.Combine(List.Select({[Contract Name],[WBS.1],[WBS.2],[WBS.3],[WBS.4]},each _<> "" and _ <> null)," & ")
),
    #"Add isParent" = Table.AddColumn(#"Add CurrentPath", "isParent", each List.Contains(
    Table.ToList(Table.Distinct(Table.SelectColumns(#"Add CurrentPath","ParentPath"))),
 [CurrentPath]))
in
    #"Add isParent"

[bolfri]

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"

[v-rzhou-msft]

Hi @rdraytonNBRS ,

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.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rdraytonNBRS]

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.

[rdraytonNBRS]

@v-rzhou-msft  - Any further thoughts on my response above? Appreciate any assistance.

[daflores]

@rdraytonNBRS Did you ever resolve this? I have a similar issue and running into the same problems

[Ashish_Mathur]

Hi,

Share some data, explain the question and show the expected result.

[daflores]

Hi,

Similar to the original post; I have a WBS Hierarchy with summary rows of the lower levels. I have not been successful in using the above technique to properly remove the summary rows and could use some help on how to modify it. Sample data below, I have hundreds of projects with varying levels of WBS hierarchy and inconsistent naming nor any parent child relationship table.

Sample data below. I need to remove the highlighted rows since those are sums of the children. Any help is appreciated, thanks.

Contract ID	Contract Name	WBS.1	WBS.2	WBS.3	WBS.4	Fee	WBS Nesting	Sort Order
2020.01	Project 1	Project 1	null	null	null	$            14,000.00	0	1
2020.01	Project 1	Task Order 1	null	null	null	$            14,000.00	1	3
2020.01	Project 1	Task Order 1	PC Coordination	null	null	$            14,000.00	2	4
2020.01	Project 1	Task Order 1	PC Coordination	Meetings and Coordination	null	$              7,000.00	3	5
2020.01	Project 1	Task Order 1	PC Coordination	Meetings and Coordination	Sub-Meeting Coordination	$              7,000.00	4	6
2020.01	Project 1	Task Order 1	PC Coordination	Cost Estimating	null	$              7,000.00	3	7
2020.01	Project 1	Task Order 1	PC Coordination	Cost Estimating	Design Estimates	$              7,000.00	4	8
2022.01	Project 2	Project 2	null	null	null	$         972,100.00	0	1
2022.01	Project 2	Task Order 1 - CO	null	null	null	$         162,100.00	1	2
2022.01	Project 2	Task Order 1 - CO	General Contract Management	null	null	$                  800.00	2	3
2022.01	Project 2	Task Order 1 - CO	Program Management	null	null	$                  500.00	2	6
2022.01	Project 2	Task Order 1 - CO	Program Support	null	null	$                  800.00	2	10
2022.01	Project 2	Task Order 1 - CO	PM Training	null	null	$            60,000.00	2	14
2022.01	Project 2	Task Order 1 - CO	PM Training	PM Training Level 1	null	$            60,000.00	3	15
2022.01	Project 2	Task Order 1 - CO	PMO Support	null	null	$         100,000.00	2	22
2022.01	Project 2	Task Order 2	null	null	null	$            95,500.00	1	35
2022.01	Project 2	Task Order 3	null	null	null	$            54,000.00	1	39
2022.01	Project 2	Task Order 4	null	null	null	$         200,000.00	1	44
2022.01	Project 2	TO5 - 3 Year Program	null	null	null	$            90,000.00	1	47
2022.01	Project 2	TO5 - 3 Year Program	General Contract Management	null	null	$              4,000.00	2	49
2022.01	Project 2	TO5 - 3 Year Program	Program Management	null	null	$            76,000.00	2	53
2022.01	Project 2	TO5 - 3 Year Program	PM Training	null	null	$            10,000.00	2	62
2022.01	Project 2	TO6	null	null	null	$            70,500.00	1	65
2022.01	Project 2	Task Order 7	null	null	null	$         300,000.00	1	70

[Ashish_Mathur]

Hi,

I do not see any pattern there at all.  So i do not know which filter condition can be applied to remove the grey coloured rows.

[daflores]

No filters, was able to accomplish with transformations and some If statements.