How to check corresponding parent and child columns to be marked with x?

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:

enter image description here

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", each 
let 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 null
in 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

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:

let
source = 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]
})
in
source

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)),

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_stackoverflow
let
source = 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]
})
in
source
// listHierarchyLevels_stackoverflow
let
source = 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]
in
col
// data_hierarchy_stackoverflow
let
source = 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])
in
source
// GetHierarchy_stackoverflow
let
source = 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])
in
AddCol_Childs
// output_stackoverflow
let
source = data_stackoverflow,
Unpivot = Table.UnpivotOtherColumns(source, {"Name"}, "Attribute","Value"),
GroupedRows = Table.Group(Unpivot, {"Name"}, {{"data", each 
let 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]
in
drilldown, type text }}),
MergedQueries = Table.NestedJoin(source, {"Name"}, GroupedRows, {"Name"}, "table2", JoinKind.LeftOuter),
ExpandedTable = Table.ExpandTableColumn(MergedQueries, "table2", {"data"}, {"Errors"})
in
ExpandedTable

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

// Check
let
source = 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], ";")
in
allErrors,
xFunction = (x, xList) =>
let
// for each x
//     check for parents
misList = List.RemoveItems(x[Parents], xList),
errorsParents = if List.Count(misList) = 0 then null
else x[Name] & "->" & Text.Combine(misList,","),
//     check for childs
errorsChilds = if List.Count(x[Childs]) = 0 or List.ContainsAny(xList, x[Childs]) then null
else x[Name] & "->(" & Text.Combine(x[Childs],"|") & ")",
err = Text.Combine({errorsParents, errorsChilds})
in
err,
result = AddCol_Error,
/*ReorderColumns = Table.ReorderColumns(ExpandedTable,
List.Combine({
{"Errors"},
List.RemoveItems(Table.ColumnNames(ExpandedTable), {"Errors"})}))*/
ReorderColumns = Table.ReorderColumns(result, List.Combine({ {"Errors", "#"}, hierarchyHeader}))
in
ReorderColumns

full code

// cache
let
Source = Excel.CurrentWorkbook(){[Name="tabCheck_preparation"]}[Content]
in
Source
// Check
let
source = 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], ";")
in
allErrors,
xFunction = (x, xList) =>
let
// for each x
//     check for parents
misList = List.RemoveItems(x[Parents], xList),
errorsParents = if List.Count(misList) = 0 then null
else x[Name] & "->" & Text.Combine(misList,","),
//     check for childs
errorsChilds = if List.Count(x[Childs]) = 0 or List.ContainsAny(xList, x[Childs]) then null
else x[Name] & "->(" & Text.Combine(x[Childs],"|") & ")",
err = Text.Combine({errorsParents, errorsChilds})
in
err,
result = AddCol_Error,
/*ReorderColumns = Table.ReorderColumns(ExpandedTable,
List.Combine({
{"Errors"},
List.RemoveItems(Table.ColumnNames(ExpandedTable), {"Errors"})}))*/
ReorderColumns = Table.ReorderColumns(result, List.Combine({ {"Errors", "#"}, hierarchyHeader}))
in
ReorderColumns
// tabHeader
let
Quelle = 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"})
in
Replace_NullBy_Kopfzeile
// GetHierarchy
let
Quelle = 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)
in
RemovedCols_Hierarchy
// listHierarchyLevels
let
Quelle = Excel.CurrentWorkbook(){[Name="tabHierarchyLevels"]}[Content],
FILTER_ja = Table.SelectRows(Quelle, each ([validate] = "ja")),
listHierarchies = FILTER_ja[listHierarchies]
in
listHierarchies