Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
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"
Proud to be a 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"
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"
Proud to be a Super User!
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.
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 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.
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 |
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.
No filters, was able to accomplish with transformations and some If statements.
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
73 | |
60 | |
58 |