Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rdraytonNBRS
Frequent Visitor

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

 

Screenshot 2023-03-14 151216.jpg

 

Cheers!

10 REPLIES 10
bolfri
Super User
Super User

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.

bolfri_0-1690931314605.png

Step 2. Add new column CurrentPath

bolfri_1-1690931348859.png

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

bolfri_2-1690931393223.png

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"

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




bolfri
Super User
Super User

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"

 

bolfri_0-1690926515937.png

 

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:

bolfri_1-1690926666267.png

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"




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




v-rzhou-msft
Community Support
Community Support

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.

RicoZhou_0-1678951657880.png

RicoZhou_1-1678951705862.png

 

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.

 

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_0-1679019808161.png

 

 

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

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

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

daflores_2-1688352200631.png

 

Contract IDContract NameWBS.1WBS.2WBS.3WBS.4 Fee WBS NestingSort Order
2020.01Project 1Project 1nullnullnull $            14,000.0001
2020.01Project 1Task Order 1nullnullnull $            14,000.0013
2020.01Project 1Task Order 1PC Coordinationnullnull $            14,000.0024
2020.01Project 1Task Order 1PC CoordinationMeetings and Coordinationnull $              7,000.0035
2020.01Project 1Task Order 1PC CoordinationMeetings and CoordinationSub-Meeting Coordination $              7,000.0046
2020.01Project 1Task Order 1PC CoordinationCost Estimatingnull $              7,000.0037
2020.01Project 1Task Order 1PC CoordinationCost EstimatingDesign Estimates $              7,000.0048
2022.01Project 2Project 2nullnullnull $         972,100.0001
2022.01Project 2Task Order 1 - COnullnullnull $         162,100.0012
2022.01Project 2Task Order 1 - COGeneral Contract Managementnullnull $                  800.0023
2022.01Project 2Task Order 1 - COProgram Managementnullnull $                  500.0026
2022.01Project 2Task Order 1 - COProgram Supportnullnull $                  800.00210
2022.01Project 2Task Order 1 - COPM Trainingnullnull $            60,000.00214
2022.01Project 2Task Order 1 - COPM TrainingPM Training Level 1null $            60,000.00315
2022.01Project 2Task Order 1 - COPMO Supportnullnull $         100,000.00222
2022.01Project 2Task Order 2nullnullnull $            95,500.00135
2022.01Project 2Task Order 3nullnullnull $            54,000.00139
2022.01Project 2Task Order 4nullnullnull $         200,000.00144
2022.01Project 2TO5 - 3 Year Programnullnullnull $            90,000.00147
2022.01Project 2TO5 - 3 Year ProgramGeneral Contract Managementnullnull $              4,000.00249
2022.01Project 2TO5 - 3 Year ProgramProgram Managementnullnull $            76,000.00253
2022.01Project 2TO5 - 3 Year ProgramPM Trainingnullnull $            10,000.00262
2022.01Project 2TO6nullnullnull $            70,500.00165
2022.01Project 2Task Order 7nullnullnull $         300,000.00170

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Helpful resources

Announcements
Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors