Search code examples
excelpowerqueryhierarchy

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

Solution

  • Well, that was fun for once, assuming this works

    (Updated Phil code input to match image)

    enter image description here

    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

    enter image description here