英文:
How to check corresponding parent and child columns to be marked with x?
问题
我有一个数据表格源，其中有各种列，可以是x或null。根据一个层次表，我想检查源是否填写正确，如果不正确，可能列出数据不正确的子父路径。
要有效的规则/检查是：如果子级为x，则所有父级必须为x，如果父级至少有一个x，则至少一个子级必须为x。
我希望有人能够理解我想要实现的内容。
以下是示例代码：
source = Table.FromRecords({[Name="Jason", A="x", B="x", C="x", D="x", E="x", F="x", G=null, H="x", I=null, J=null, K=null, L="x", M=null],[Name="Joe", A="x", B=null, C="x", D=null, E=null, F=null, G="x", H="x", I=null, J=null, K=null, L=null, M="x"],[Name="Eddie", A="x", B=null, C="x", D=null, E=null, F="x", G=null, H="x", I=null, J="x", K=null, L=null, M=null],[Name="Phil", A=null, B=null, C=null, D="x", E=null, F=null, G=null, H=null, I=null, J=null, K=null, L="x", M=null],[Name="Thomas", A="x", B=null, C=null, D=null, E=null, F="x", G="x", H="x", I=null, J=null, K=null, L=null, M=null],[Name="David", A="x", B=null, C=null, D=null, E="x", F="x", G=null, H=null, I=null, J=null, K=null, L=null, M=null],[Name="Matthew", A=null, B="x", C=null, D=null, E=null, F=null, G=null, H=null, I=null, J=null, K="x", L=null, M=null]}),hierarchy = Table.FromRecords({[Column1 = "A", Hierarchy = {null}],[Column1 = "B", Hierarchy = {"A"}],[Column1 = "C", Hierarchy = {"A"}],[Column1 = "D", Hierarchy = {"A", "C"}],[Column1 = "E", Hierarchy = {"A", "C"}],[Column1 = "F", Hierarchy = {"A"}],[Column1 = "G", Hierarchy = {"A", "F"}],[Column1 = "H", Hierarchy = {"A", "F"}],[Column1 = "I", Hierarchy = {null}],[Column1 = "J", Hierarchy = {"I"}],[Column1 = "K", Hierarchy = {"I"}],[Column1 = "L", Hierarchy = {"I", "K"}],[Column1 = "M", Hierarchy = {"I", "K"}]}),
这里是一些手动比较的屏幕截图：链接。
英文:
I've got a data table source, with various columns that could be either x or null. Depending on a hierarchy table, I would like to check the source whether it is filled out correctly, and if not maybe list the child to parent path where the data is incorrect.
The rule / check to be valid would be: if a child is x, all parents have to be x if a parent is x at least one child has to be x
I hope somebody is able to figure out, what I would like to achieve.
source = Table.FromRecords({[Name="Jason", A="x", B="x", C="x", D="x", E="x", F="x", G=null, H="x", I=null, J=null, K=null, L="x", M=null],[Name="Joe", A="x", B=null, C="x", D=null, E=null, F=null, G="x", H="x", I=null, J=null, K=null, L=null, M="x"],[Name="Eddie", A="x", B=null, C="x", D=null, E=null, F="x", G=null, H="x", I=null, J="x", K=null, L=null, M=null],[Name="Phil", A=null, B=null, C=null, D="x", E=null, F=null, G=null, H=null, I=null, J=null, K=null, L="x", M=null],[Name="Thomas", A="x", B=null, C=null, D=null, E=null, F="x", G="x", H="x", I=null, J=null, K=null, L=null, M=null],[Name="David", A="x", B=null, C=null, D=null, E="x", F="x", G=null, H=null, I=null, J=null, K=null, L=null, M=null],[Name="Matthew", A=null, B="x", C=null, D=null, E=null, F=null, G=null, H=null, I=null, J=null, K="x", L=null, M=null]}),hierarchy = Table.FromRecords({[Column1 = "A", Hierarchy = {null}],[Column1 = "B", Hierarchy = {"A"}],[Column1 = "C", Hierarchy = {"A"}],[Column1 = "D", Hierarchy = {"A", "C"}],[Column1 = "E", Hierarchy = {"A", "C"}],[Column1 = "F", Hierarchy = {"A"}],[Column1 = "G", Hierarchy = {"A", "F"}],[Column1 = "H", Hierarchy = {"A", "F"}],[Column1 = "I", Hierarchy = {null}],[Column1 = "J", Hierarchy = {"I"}],[Column1 = "K", Hierarchy = {"I"}],[Column1 = "L", Hierarchy = {"I", "K"}],[Column1 = "M", Hierarchy = {"I", "K"}]}),
Here is a screenshot of some manual comparison:
答案1
得分: 0
以下是翻译好的代码部分：
let source = Table.FromRecords({[Name="Jason", A="x", B="x", C="x", D="x", E="x", F="x", G=null, H="x", I=null, J=null, K=null, L="x", M=null],[Name="Joe", A="x", B=null, C="x", D=null, E=null, F=null, G="x", H="x", I=null, J=null, K=null, L=null, M="x"],[Name="Eddie", A="x", B=null, C="x", D=null, E=null, F="x", G=null, H="x", I=null, J="x", K=null, L=null, M=null],[Name="Phil", A=null, B=null, C=null, D="x", E=null, F=null, G=null, H=null, I="x", J=null, K="x", L="x", M=null],[Name="Thomas", A="x", B=null, C=null, D=null, E=null, F="x", G="x", H="x", I=null, J=null, K=null, L=null, M=null],[Name="David", A="x", B=null, C=null, D=null, E="x", F="x", G=null, H=null, I=null, J=null, K=null, L=null, M=null],[Name="Matthew", A=null, B="x", C=null, D=null, E=null, F=null, G=null, H=null, I=null, J=null, K="x", L=null, M=null]}),hierarchy = Table.FromRecords({[Column1 = "A", Hierarchy = {null}],[Column1 = "B", Hierarchy = {"A"}],[Column1 = "C", Hierarchy = {"A"}],[Column1 = "D", Hierarchy = {"A", "C"}],[Column1 = "E", Hierarchy = {"A", "C"}],[Column1 = "F", Hierarchy = {"A"}],[Column1 = "G", Hierarchy = {"A", "F"}],[Column1 = "H", Hierarchy = {"A", "F"}],[Column1 = "I", Hierarchy = {null}],[Column1 = "J", Hierarchy = {"I"}],[Column1 = "K", Hierarchy = {"I"}],[Column1 = "L", Hierarchy = {"I", "K"}],[Column1 = "M", Hierarchy = {"I", "K"}]}),#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(source, {"Name"}, "Attribute","Value"),#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Name"}, {"data", eachlet a = Table.NestedJoin(_, {"Attribute"}, hierarchy, {"Column1"}, "hierarchy", JoinKind.LeftOuter),b = Table.ExpandTableColumn(a, "hierarchy", {"Hierarchy"}, {"David"}),c = Table.ExpandListColumn(b, "David"),d = Table.NestedJoin(c, {"David"}, c, {"Attribute"}, "Ron", JoinKind.LeftOuter),e = Table.ExpandTableColumn(d, "Ron", {"Attribute"}, {"Peter"}),f = Table.SelectRows(e, each ([David] <> null) and ([Peter] = null)),g= Table.Group(f, {"Name","Attribute"}, {"Concat", each Text.Combine([David],", "), type text}),h = Table.AddColumn(g, "Custom", each [Attribute] & "->" & [Concat], type text),i= try Table.Group( h, {"Name"}, {"Concat", each Text.Combine([Custom],"; "), type text}) {0}[Concat] otherwise nullin i , type text }),#"Merged Queries" = Table.NestedJoin(source, {"Name"},#"Grouped Rows", {"Name"}, "table2", JoinKind.LeftOuter),#"Expanded table2" = Table.ExpandTableColumn(#"Merged Queries", "table2", {"data"}, {"Errors"})in #"Expanded table2"
如果只需要错误列表，可以删除最后两个步骤。
英文:
Well, that was fun for once, assuming this works
(Updated Phil code input to match image)
let source = Table.FromRecords({[Name="Jason", A="x", B="x", C="x", D="x", E="x", F="x", G=null, H="x", I=null, J=null, K=null, L="x", M=null],[Name="Joe", A="x", B=null, C="x", D=null, E=null, F=null, G="x", H="x", I=null, J=null, K=null, L=null, M="x"],[Name="Eddie", A="x", B=null, C="x", D=null, E=null, F="x", G=null, H="x", I=null, J="x", K=null, L=null, M=null],[Name="Phil", A=null, B=null, C=null, D="x", E=null, F=null, G=null, H=null, I="x", J=null, K="x", L="x", M=null],[Name="Thomas", A="x", B=null, C=null, D=null, E=null, F="x", G="x", H="x", I=null, J=null, K=null, L=null, M=null],[Name="David", A="x", B=null, C=null, D=null, E="x", F="x", G=null, H=null, I=null, J=null, K=null, L=null, M=null],[Name="Matthew", A=null, B="x", C=null, D=null, E=null, F=null, G=null, H=null, I=null, J=null, K="x", L=null, M=null]}),hierarchy = Table.FromRecords({[Column1 = "A", Hierarchy = {null}],[Column1 = "B", Hierarchy = {"A"}],[Column1 = "C", Hierarchy = {"A"}],[Column1 = "D", Hierarchy = {"A", "C"}],[Column1 = "E", Hierarchy = {"A", "C"}],[Column1 = "F", Hierarchy = {"A"}],[Column1 = "G", Hierarchy = {"A", "F"}],[Column1 = "H", Hierarchy = {"A", "F"}],[Column1 = "I", Hierarchy = {null}],[Column1 = "J", Hierarchy = {"I"}],[Column1 = "K", Hierarchy = {"I"}],[Column1 = "L", Hierarchy = {"I", "K"}],[Column1 = "M", Hierarchy = {"I", "K"}]}),#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(source, {"Name"}, "Attribute","Value"),#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Name"}, {{"data", eachlet a = Table.NestedJoin(_, {"Attribute"}, hierarchy, {"Column1"}, "hierarchy", JoinKind.LeftOuter),b = Table.ExpandTableColumn(a,"hierarchy", {"Hierarchy"}, {"David"}),c = Table.ExpandListColumn(b, "David"),d = Table.NestedJoin(c, {"David"}, c, {"Attribute"}, "Ron", JoinKind.LeftOuter),e = Table.ExpandTableColumn(d, "Ron", {"Attribute"}, {"Peter"}),f = Table.SelectRows(e, each ([David] <> null) and ([Peter] = null)),g= Table.Group(f, {"Name","Attribute"}, {{"Concat", each Text.Combine([David],","), type text}}),h = Table.AddColumn(g, "Custom", each [Attribute]&"->"&[Concat],type text),i= try Table.Group( h, {"Name"}, {{"Concat", each Text.Combine([Custom],";"), type text}}) {0}[Concat] otherwise nullin i , type text }}),#"Merged Queries" = Table.NestedJoin(source, {"Name"},#"Grouped Rows", {"Name"}, "table2", JoinKind.LeftOuter),#"Expanded table2" = Table.ExpandTableColumn(#"Merged Queries", "table2", {"data"}, {"Errors"})in #"Expanded table2"
you could delete last two steps if you just want the error list
答案2
得分: 0
我目前正在努力修复/实现提供的代码中的以下情景：
same hierarchy, but data sample with parent without childs
层次结构与上述帖子中的初始层次结构相同。
数据源已更改为：
letsource = Table.FromRecords({[Name="Jason", A="x", B="x", C="x", D="x", E="x", F="x", G=null, H=">x<", I="x", J=null, K=null, L=null, M=null],[Name="Joe", A="x", B=null, C="x", D=null, E=null, F=null, G="x", H="x", I=null, J=null, K=null, L=null, M="x"],[Name="Eddie", A="x", B=null, C="x", D=null, E=null, F="x", G=null, H=">x<", I=null, J="x", K=null, L=null, M=null],[Name="Phil", A=null, B=null, C=null, D="x", E=null, F=null, G=null, H=null, I="x", J=null, K="x", L="x", M=null],[Name="Thomas", A="x", B=null, C=null, D=null, E=null, F="x", G="x", H="x", I=null, J=null, K=null, L=null, M=null],[Name="David", A="x", B=null, C=null, D=null, E="x", F="x", G=null, H=null, I=null, J=null, K=null, L=null, M=null],[Name="Matthew", A=null, B="x", C=null, D=null, E=null, F=null, G=null, H=null, I=null, J=null, K="x", L=null, M=null]})insource
预期的结果应该是对于以下情况也有一个错误文本：
- 父节点标记为 x，
- 根据层次结构，它有子节点，
- 但没有任何子节点标记为 x。
--> 至少预计其中一个子节点也应该标记为 x。
我的第一个想法是在＃“Grouped Rows”=中添加附加步骤，但我预计我可能会引入不必要的步骤，因此可能效率不高。
f2 = Table.SelectRows(e, each ([David] = null) and ([Peter] = null)),
英文:
I am struggling currently to fix / implement the following scenario in the provided code:
same hierarchy, but data sample with parent without childs
hierarchy is the initial one from posts above.
data source is changed to:
letsource = Table.FromRecords({[Name="Jason", A="x", B="x", C="x", D="x", E="x", F="x", G=null, H="x", I="x", J=null, K=null, L=null, M=null],[Name="Joe", A="x", B=null, C="x", D=null, E=null, F=null, G="x", H="x", I=null, J=null, K=null, L=null, M="x"],[Name="Eddie", A="x", B=null, C="x", D=null, E=null, F="x", G=null, H="x", I=null, J="x", K=null, L=null, M=null],[Name="Phil", A=null, B=null, C=null, D="x", E=null, F=null, G=null, H=null, I="x", J=null, K="x", L="x", M=null],[Name="Thomas", A="x", B=null, C=null, D=null, E=null, F="x", G="x", H="x", I=null, J=null, K=null, L=null, M=null],[Name="David", A="x", B=null, C=null, D=null, E="x", F="x", G=null, H=null, I=null, J=null, K=null, L=null, M=null],[Name="Matthew", A=null, B="x", C=null, D=null, E=null, F=null, G=null, H=null, I=null, J=null, K="x", L=null, M=null]})insource
The expected result would be to have also an error text for those case where
- a parent is marked with x,
- according to hierarchy it has childs,
- but none of the childs are marked with x.
--> at least one of the childs is expected to be marked as well.
My first idea was to add additional steps in the #"Grouped Rows" = but I expect somehow that I am gonne introduce to much steps which might not be needed and thus might be inefficient.
f2 = Table.SelectRows(e, each ([David] = null) and ([Peter] = null)),
答案3
得分: 0
我已经能够使用示例数据和分层表修复了关于父级和子级方向的错误处理。
再次感谢 @horseyride 提供了关于父级方向的解决方案，我能够重用该结构来支持我的新方法。
以下是示例查询的代码部分：
// data_stackoverflowletsource = Table.FromRecords({[Name="Jason", A="x", B="x", C="x", D="x", E="x", F="x", G=null, H="x", I="x", J=null, K=null, L=null, M=null],[Name="Joe", A="x", B=null, C="x", D=null, E=null, F=null, G="x", H="x", I=null, J=null, K=null, L=null, M="x"],[Name="Eddie", A="x", B=null, C="x", D=null, E=null, F="x", G=null, H="x", I=null, J="x", K=null, L=null, M=null],[Name="Phil", A=null, B=null, C=null, D="x", E=null, F=null, G=null, H=null, I="x", J=null, K="x", L="x", M=null],[Name="Thomas", A="x", B=null, C=null, D=null, E=null, F="x", G="x", H="x", I=null, J=null, K=null, L=null, M=null],[Name="David", A="x", B=null, C=null, D=null, E="x", F="x", G=null, H=null, I=null, J=null, K=null, L=null, M=null],[Name="Matthew", A=null, B="x", C=null, D=null, E=null, F=null, G=null, H=null, I=null, J=null, K="x", L=null, M=null]})insource// listHierarchyLevels_stackoverflowletsource = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jFUitUBUkYQylgpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col = _t]),col = source[col]incol// data_hierarchy_stackoverflowletsource = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc67CcAwDATQXVR7ifj/nUBoA+Mu+ycIBa5IIe54cCBmusjpnXvvL8QxeXOPGAwDYgSMKgkkqWSTjMMCWFQqSFVpb2s/73XzjjgMB+IEnCoLZJHIAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [header = _t, L1 = _t, L2 = _t, L3 = _t])insource// GetHierarchy_stackoverflowletsource = data_hierarchy_stackoverflow,HierarchyLevels = listHierarchyLevels_stackoverflow,FILTER_HierarchyOnly = Table.SelectRows(source, each Record.Field(_, HierarchyLevels{0}) <> null and Record.Field(_, HierarchyLevels{0}) <> ""),AddCol_Parents = Table.AddColumn(FILTER_HierarchyOnly, "Parents", each List.RemoveItems(List.RemoveNulls(Record.ToList(Record.SelectFields(_, HierarchyLevels))), {_[header]})),AddCol_LevelNo = Table.AddColumn(AddCol_Parents, "LevelNo", each List.Count(_[Parents])+1),AddCol_Level = Table.AddColumn(AddCol_LevelNo, "Level", each HierarchyLevels{_[LevelNo]-1}),AddCol_Childs = Table.AddColumn(AddCol_Level, "Childs", (r1) =>Table.SelectRows(AddCol_Level, (r2) => (Record.Field(r2, r1[Level]) = r1[header]) and (r2[LevelNo] = r1[LevelNo]+1))[header])inAddCol_Childs// output_stackoverflowletsource = data_stackoverflow,Unpivot = Table.UnpivotOtherColumns(source, {"Name"}, "Attribute","Value"),GroupedRows = Table.Group(Unpivot, {"Name"}, {{"data", eachlet source = _,JOIN_hierarchy = Table.NestedJoin(source, {"Attribute"}, GetHierarchy_stackoverflow, {"header"}, "hierarchy", JoinKind.LeftOuter),Expand_hierarchy = let cols = Table.ColumnNames(GetHierarchy_stackoverflow) in Table.ExpandTableColumn(JOIN_hierarchy, "hierarchy", cols, cols),AddCol_ErrorsParents = Table.AddColumn(Expand_hierarchy, "ErrorsParents", each let misList = List.RemoveItems(_[Parents], Expand_hierarchy[Attribute]) in if List.Count(misList) > 0 then [Attribute] & "->" & Text.Combine(misList,",") else null),AddCol_ErrorsChilds = Table.AddColumn(AddCol_ErrorsParents, "ErrorsChilds", each if List.Count(_[Childs]) = 0 or List.ContainsAny(AddCol_ErrorsParents[Attribute], _[Childs]) then null else [Attribute] & "->(" & Text.Combine(_[Childs],"|") & ")"),AddCol_Error = Table.AddColumn(AddCol_ErrorsChilds, "Errors", each let err = Text.Combine({[ErrorsParents], [ErrorsChilds]}, ",") in if err = "" then null else err),Group_ConcatErrors_ByName = Table.Group(AddCol_Error, {"Name"}, {"Concat", each Text.Combine([Errors],";"), type text}),drilldown = Group_ConcatErrors_ByName{0}[Concat]indrilldown, type text }}),MergedQueries = Table.NestedJoin(source, {"Name"}, GroupedRows, {"Name"}, "table2", JoinKind.LeftOuter),ExpandedTable = Table.ExpandTableColumn(MergedQueries, "table2", {"data"}, {"Errors"})inExpandedTable
希望这对你有所帮助。如果有任何其他问题，请随时提出。
英文:
I was able to fix the error handling in the direction of parents as well as childs using sample data and an hierarchy table.
Thanks again @horseyride for your solution in the direction of parents, I was able to reuse the structure for my new approach.
following the sample queries:
// data_stackoverflowletsource = Table.FromRecords({[Name="Jason", A="x", B="x", C="x", D="x", E="x", F="x", G=null, H="x", I="x", J=null, K=null, L=null, M=null],[Name="Joe", A="x", B=null, C="x", D=null, E=null, F=null, G="x", H="x", I=null, J=null, K=null, L=null, M="x"],[Name="Eddie", A="x", B=null, C="x", D=null, E=null, F="x", G=null, H="x", I=null, J="x", K=null, L=null, M=null],[Name="Phil", A=null, B=null, C=null, D="x", E=null, F=null, G=null, H=null, I="x", J=null, K="x", L="x", M=null],[Name="Thomas", A="x", B=null, C=null, D=null, E=null, F="x", G="x", H="x", I=null, J=null, K=null, L=null, M=null],[Name="David", A="x", B=null, C=null, D=null, E="x", F="x", G=null, H=null, I=null, J=null, K=null, L=null, M=null],[Name="Matthew", A=null, B="x", C=null, D=null, E=null, F=null, G=null, H=null, I=null, J=null, K="x", L=null, M=null]})insource// listHierarchyLevels_stackoverflowletsource = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jFUitUBUkYQylgpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col = _t]),col = source[col]incol// data_hierarchy_stackoverflowletsource = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc67CcAwDATQXVR7ifj/nUBoA+Mu+ycIBa5IIe54cCBmusjpnXvvL8QxeXOPGAwDYgSMKgkkqWSTjMMCWFQqSFVpb2s/73XzjjgMB+IEnCoLZJHIAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [header = _t, L1 = _t, L2 = _t, L3 = _t])insource// GetHierarchy_stackoverflowletsource = data_hierarchy_stackoverflow,HierarchyLevels = listHierarchyLevels_stackoverflow,FILTER_HierarchyOnly = Table.SelectRows(source, each Record.Field(_, HierarchyLevels{0}) <> null and Record.Field(_, HierarchyLevels{0}) <> ""),AddCol_Parents = Table.AddColumn(FILTER_HierarchyOnly, "Parents", each List.RemoveItems(List.RemoveNulls(Record.ToList(Record.SelectFields(_, HierarchyLevels))), {_[header]})),AddCol_LevelNo = Table.AddColumn(AddCol_Parents, "LevelNo", each List.Count(_[Parents])+1),AddCol_Level = Table.AddColumn(AddCol_LevelNo, "Level", each HierarchyLevels{_[LevelNo]-1}),AddCol_Childs = Table.AddColumn(AddCol_Level, "Childs", (r1) =>Table.SelectRows(AddCol_Level, (r2) => (Record.Field(r2, r1[Level]) = r1[header]) and (r2[LevelNo] = r1[LevelNo]+1))[header])inAddCol_Childs// output_stackoverflowletsource = data_stackoverflow,Unpivot = Table.UnpivotOtherColumns(source, {"Name"}, "Attribute","Value"),GroupedRows = Table.Group(Unpivot, {"Name"}, {{"data", eachlet source = _,JOIN_hierarchy = Table.NestedJoin(source, {"Attribute"}, GetHierarchy_stackoverflow, {"header"}, "hierarchy", JoinKind.LeftOuter),Expand_hierarchy = let cols = Table.ColumnNames(GetHierarchy_stackoverflow) in Table.ExpandTableColumn(JOIN_hierarchy, "hierarchy", cols, cols),AddCol_ErrorsParents = Table.AddColumn(Expand_hierarchy, "ErrorsParents", each let misList = List.RemoveItems(_[Parents], Expand_hierarchy[Attribute]) in if List.Count(misList) > 0 then [Attribute]&"->"&Text.Combine(misList,",") else null),AddCol_ErrorsChilds = Table.AddColumn(AddCol_ErrorsParents, "ErrorsChilds", each if List.Count(_[Childs]) = 0 or List.ContainsAny(AddCol_ErrorsParents[Attribute], _[Childs]) then null else [Attribute]&"->("&Text.Combine(_[Childs],"|")&")"),AddCol_Error = Table.AddColumn(AddCol_ErrorsChilds, "Errors", each let err = Text.Combine({[ErrorsParents], [ErrorsChilds]}, ",") in if err = "" then null else err),Group_ConcatErrors_ByName = Table.Group(AddCol_Error, {"Name"}, {"Concat", each Text.Combine([Errors],";"), type text}),drilldown = Group_ConcatErrors_ByName{0}[Concat]indrilldown, type text }}),MergedQueries = Table.NestedJoin(source, {"Name"}, GroupedRows, {"Name"}, "table2", JoinKind.LeftOuter),ExpandedTable = Table.ExpandTableColumn(MergedQueries, "table2", {"data"}, {"Errors"})inExpandedTable
答案4
得分: 0
以下是您要翻译的代码部分：
Okay I tried to optimized a little further for readability but mainly having both directions checked- parents --> all parents must be enabled- child --> at least one child has to be enabledI tried to find out how to reduce the access to the source file which is either a 1000x15 table in external Excel or even on the same file as tab. Preview are working nice and smooth as long as I keep out of business allErrors = Text.Combine(onlyErrors[Errors], ";").main code - check query
// Checkletsource = cache,hierarchy = Table.Buffer(GetHierarchy),hierarchyHeader = hierarchy[header],hierarchyCols = List.Buffer(Table.ColumnNames(hierarchy)),...
full code
// cacheletSource = Excel.CurrentWorkbook(){[Name="tabCheck_preparation"]}[Content]inSource// Checkletsource = cache,hierarchy = Table.Buffer(GetHierarchy),hierarchyHeader = hierarchy[header],hierarchyCols = List.Buffer(Table.ColumnNames(hierarchy)),...
// tabHeaderletQuelle = Excel.CurrentWorkbook(){[Name="tabHeader"]}[Content],FILTER_relevantOnly = Table.SelectRows(Quelle, each ([Übernahme] = "ja")),Replace_NullBy_Kopfzeile = Table.ReplaceValue(FILTER_relevantOnly,null, each [Kopfzeile], Replacer.ReplaceValue, {"Kopfzeile_Renamed"})inReplace_NullBy_Kopfzeile
// GetHierarchyletQuelle = tabHeader,HierarchyLevels = listHierarchyLevels,FILTER_HierarchyLevels = Table.SelectRows(Quelle, each List.Contains(HierarchyLevels, [hierarchy])),KEEP_HierarchyColumns = Table.SelectColumns(FILTER_HierarchyLevels, List.Combine({{"Kopfzeile_Renamed"}, HierarchyLevels})),RenameCol_header = Table.RenameColumns(KEEP_HierarchyColumns,{{"Kopfzeile_Renamed", "header"}}),Replace_EmptyBy_Null = Table.ReplaceValue(RenameCol_header,"",null,Replacer.ReplaceValue, listHierarchyLevels),...
// listHierarchyLevelsletQuelle = Excel.CurrentWorkbook(){[Name="tabHierarchyLevels"]}[Content],FILTER_ja = Table.SelectRows(Quelle, each ([validate] = "ja")),listHierarchies = FILTER_ja[listHierarchies]inlistHierarchies
希望这有所帮助。
英文:
Okay I tried to optimized a little further for readability but mainly having both directions checked
- parents --> all parents must be enabled
- child --> at least one child has to be enabled
I tried to find out how to reduce the access to the source file which is either a 1000x15 table in external Excel or even on the same file as tab.
Preview are working nice and smooth as long as I keep out of business
allErrors = Text.Combine(onlyErrors[Errors], ";").
main code - check query
// Checkletsource = cache,hierarchy = Table.Buffer(GetHierarchy),hierarchyHeader = hierarchy[header],hierarchyCols = List.Buffer(Table.ColumnNames(hierarchy)),/*source = cache,hierarchy = GetHierarchy,hierarchyHeader = hierarchy[header],hierarchyCols = List.Buffer(Table.ColumnNames(hierarchy)),*/AddCol_Error = Table.AddColumn(source, "Errors", each bomFunction(_, hierarchy, hierarchyCols)),bomFunction = (bomRow, bHierarchy, bHierarchyCols) =>let// for each row in table (BOM #)xTable = Table.SelectRows(Record.ToTable(bomRow), each [Value] = "x"),xTableKey = Table.AddKey(xTable, {"Name"}, true),JOIN_hierarchy = Table.NestedJoin(xTable, {"Name"}, bHierarchy, "header", "hierarchy", JoinKind.LeftOuter),Expand_hierarchy = Table.ExpandTableColumn(JOIN_hierarchy, "hierarchy", bHierarchyCols, bHierarchyCols),xList = List.Buffer(Expand_hierarchy[Name]),AddCol_Errors = Table.AddColumn(Expand_hierarchy, "Errors", each xFunction(_, xList)),onlyErrors = Table.SelectRows(AddCol_Errors, each [Errors] <> null and [Errors] <> ""),//Group_ConcatErrors_ByName = Table.Group(AddCol_Error, {colIndex}, {"Concat", each Text.Combine([Errors],";"), type text}),allErrors = Text.Combine(onlyErrors[Errors], ";")inallErrors,xFunction = (x, xList) =>let// for each x// check for parentsmisList = List.RemoveItems(x[Parents], xList),errorsParents = if List.Count(misList) = 0 then nullelse x[Name] & "->" & Text.Combine(misList,","),// check for childserrorsChilds = if List.Count(x[Childs]) = 0 or List.ContainsAny(xList, x[Childs]) then nullelse x[Name] & "->(" & Text.Combine(x[Childs],"|") & ")",err = Text.Combine({errorsParents, errorsChilds})inerr,result = AddCol_Error,/*ReorderColumns = Table.ReorderColumns(ExpandedTable,List.Combine({{"Errors"},List.RemoveItems(Table.ColumnNames(ExpandedTable), {"Errors"})}))*/ReorderColumns = Table.ReorderColumns(result, List.Combine({ {"Errors", "#"}, hierarchyHeader}))inReorderColumns
full code
// cacheletSource = Excel.CurrentWorkbook(){[Name="tabCheck_preparation"]}[Content]inSource// Checkletsource = cache,hierarchy = Table.Buffer(GetHierarchy),hierarchyHeader = hierarchy[header],hierarchyCols = List.Buffer(Table.ColumnNames(hierarchy)),/*source = cache,hierarchy = GetHierarchy,hierarchyHeader = hierarchy[header],hierarchyCols = List.Buffer(Table.ColumnNames(hierarchy)),*/AddCol_Error = Table.AddColumn(source, "Errors", each bomFunction(_, hierarchy, hierarchyCols)),bomFunction = (bomRow, bHierarchy, bHierarchyCols) =>let// for each row in table (BOM #)xTable = Table.SelectRows(Record.ToTable(bomRow), each [Value] = "x"),xTableKey = Table.AddKey(xTable, {"Name"}, true),JOIN_hierarchy = Table.NestedJoin(xTable, {"Name"}, bHierarchy, "header", "hierarchy", JoinKind.LeftOuter),Expand_hierarchy = Table.ExpandTableColumn(JOIN_hierarchy, "hierarchy", bHierarchyCols, bHierarchyCols),xList = List.Buffer(Expand_hierarchy[Name]),AddCol_Errors = Table.AddColumn(Expand_hierarchy, "Errors", each xFunction(_, xList)),onlyErrors = Table.SelectRows(AddCol_Errors, each [Errors] <> null and [Errors] <> ""),//Group_ConcatErrors_ByName = Table.Group(AddCol_Error, {colIndex}, {"Concat", each Text.Combine([Errors],";"), type text}),allErrors = Text.Combine(onlyErrors[Errors], ";")inallErrors,xFunction = (x, xList) =>let// for each x// check for parentsmisList = List.RemoveItems(x[Parents], xList),errorsParents = if List.Count(misList) = 0 then nullelse x[Name] & "->" & Text.Combine(misList,","),// check for childserrorsChilds = if List.Count(x[Childs]) = 0 or List.ContainsAny(xList, x[Childs]) then nullelse x[Name] & "->(" & Text.Combine(x[Childs],"|") & ")",err = Text.Combine({errorsParents, errorsChilds})inerr,result = AddCol_Error,/*ReorderColumns = Table.ReorderColumns(ExpandedTable,List.Combine({{"Errors"},List.RemoveItems(Table.ColumnNames(ExpandedTable), {"Errors"})}))*/ReorderColumns = Table.ReorderColumns(result, List.Combine({ {"Errors", "#"}, hierarchyHeader}))inReorderColumns// tabHeaderletQuelle = Excel.CurrentWorkbook(){[Name="tabHeader"]}[Content],FILTER_relevantOnly = Table.SelectRows(Quelle, each ([Übernahme] = "ja")),Replace_NullBy_Kopfzeile = Table.ReplaceValue(FILTER_relevantOnly,null, each [Kopfzeile], Replacer.ReplaceValue, {"Kopfzeile_Renamed"})inReplace_NullBy_Kopfzeile// GetHierarchyletQuelle = tabHeader,HierarchyLevels = listHierarchyLevels,FILTER_HierarchyLevels = Table.SelectRows(Quelle, each List.Contains(HierarchyLevels, [hierarchy])),KEEP_HierarchyColumns = Table.SelectColumns(FILTER_HierarchyLevels, List.Combine({{"Kopfzeile_Renamed"}, HierarchyLevels})),RenameCol_header = Table.RenameColumns(KEEP_HierarchyColumns,{{"Kopfzeile_Renamed", "header"}}),Replace_EmptyBy_Null = Table.ReplaceValue(RenameCol_header,"",null,Replacer.ReplaceValue, listHierarchyLevels),AddCol_Parents = Table.AddColumn(Replace_EmptyBy_Null, "Parents", each List.RemoveItems(List.RemoveNulls(Record.ToList(Record.SelectFields(_, HierarchyLevels))), {_[header]})),AddCol_LevelNo = Table.AddColumn(AddCol_Parents, "LevelNo", each List.Count(_[Parents])+1),AddCol_Level = Table.AddColumn(AddCol_LevelNo, "Level", each HierarchyLevels{_[LevelNo]-1}),#"Removed Errors" = Table.RemoveRowsWithErrors(AddCol_Level),FILTER_maxLevel = Table.SelectRows(#"Removed Errors", let maxLevel = List.Count(HierarchyLevels) in each ([LevelNo] <= maxLevel)),AddCol_Childs = Table.AddColumn(FILTER_maxLevel, "Childs", (r1) =>Table.SelectRows(FILTER_maxLevel, (r2) => (Record.Field(r2, r1[Level]) = r1[header]) and (r2[LevelNo] = r1[LevelNo]+1))[header]),RemovedCols_Hierarchy = Table.RemoveColumns(AddCol_Childs, HierarchyLevels)inRemovedCols_Hierarchy// listHierarchyLevelsletQuelle = Excel.CurrentWorkbook(){[Name="tabHierarchyLevels"]}[Content],FILTER_ja = Table.SelectRows(Quelle, each ([validate] = "ja")),listHierarchies = FILTER_ja[listHierarchies]inlistHierarchies
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论