Accounting for disabled parents in a table matrix hierarchy

[Anonymous]

This is a followup to Determining if a parent in a hierarchy is disabled, for multiple test cases. (Thanks again @ImkeF!)

I have a security matrix report in this format:

ID	L1	L2	L3	L4	L5	L6	L7	Level	group1	group2
1	Menu1							1	N	Y
2	Menu1	Sub1						2	N	N
3	Menu1	Sub2						2	Y	N
4	Menu2							1	Y	Y
5	Menu2	Sub1						2	N	N
6	Menu2	Sub1	SubSub1					3	Y	Y
7	Menu2	Sub2						2	N	Y
8	Menu2	Sub2	SubSub1					3	Y	N
9	Menu2	Sub2	SubSub1	SubSubSub1				4	Y	Y

L1 to L7 are string values that correspond to clickpaths. In the system for which this matrix corresponds, if a parent is disabled, the children are implictly disabled. However, the report matrix does not account for this implicit hierarchy. It may show a child as enabled even if the parent is disabled.

What I'd like is to transform that matrix into this one:

ID	L1	L2	L3	L4	L5	L6	L7	Level	group1	group2
1	Menu1							1	N	Y
2	Menu1	Sub1						2	N	N
3	Menu1	Sub2						2	N	N
4	Menu2							1	Y	Y
5	Menu2	Sub1						2	N	N
6	Menu2	Sub1	SubSub1					3	N	N
7	Menu2	Sub2						2	N	Y
8	Menu2	Sub2	SubSub1					3	N	N
9	Menu2	Sub2	SubSub1	SubSubSub1				4	N	N

Essentially the same, except for each group, the children of disabled parents are also shown as disabled. 

Some notes from earlier: 

1500 rows
Up to 50 groups
Unique IDs, sequential
Unique group names, dynamic number of groups
Row/group associations can be "Y" or "N" (no nulls in this example)

"Level" column shows the deepest level of each row

Is there an efficient way to do this? I've tried to merge the 'find parents' logic from the earlier post, but have not been successful so far. 

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[Anonymous]

@v-lid-msft, same result unfortunately. With 50 groups and an abbreviated list of 13 items, it loads at about 2 seconds per row on my 7700K. 

[Anonymous]

@v-lid-msft, I was able to solve this at scale with a variation of the query provided by @ImkeF in the header. Thank you for trying this though! I think your solution is very elegant for smaller datasets. 

[v-lid-msft]

Hi @Anonymous ,

Sorry for that we cannot understand the transform logic, does the group1 and group2 already exist in the table and does there will be more that two group column? Could you please explain the logic for ID 6 to 9 further?

Best regards,

[Anonymous]

@v-lid-msft, sure, let me explain better. 

The first matrix is the input and represents a permission matrix. The first nine columns will always exist, named as in the sample. There can be 0-50 group columns following, all with arbitrary (but unique) names. Each Y/N reports whether the deepest level on the row (e.g., for Menu2Sub2 / SubSub1, the SubSub1) would be accessible if the row were not grouped in hierarchy. 

The problem is, there is a hierarchy, and a child that's enabled won't be visible if the parent is disabled. I'm trying to change each Y to an N if any of the parent rows are marked N. That's what the second matrix shows.

ID3, group 1: this becomes N because parent ID1 is N

ID6, group 1: this becomes N because parent ID5 is N

ID8, group 1: this becomes N because parent ID7 is N

ID9, group 1: this becomes N because parent ID7 is N

Group 2 logic is the same. Any row can have up to six parents. 

[v-lid-msft]

Hi @Anonymous ,

We can try to use the following steps to meet your requirement:

1. unpivot all the group coulmn:

2. Rename them as "GroupName" and "GroupValuue" 

3. add four custom columns

 1) Level.1

Text.Combine(Record.ToList(let le = [Level]
in
Record.SelectFields(_,
    List.Select(
        Record.FieldNames(_), 
        each  List.Contains(
            List.Transform({1..le},each Text.From(_)),
           Text.Replace(_,"L","")
            )
        )
    )),",")

 2) PreviousID

let currentLevel = [Level.1], le = [Level]
in
Table.Distinct(Table.SelectColumns(Table.SelectRows(#"Added Custom", each Text.StartsWith(currentLevel,[Level.1]) and le >= [Level]+1), {"ID"}))

 3) PreviousGroupValueTable

let GN = [GroupName]
in
Table.AddColumn([PreviousID],"PreviousGroupVaule",
each
let i = [ID],
temp = 
Table.Max(Table.SelectRows(#"Added Custom1", each [ID] = i and GN = [GroupName]),"GroupValue")
in if temp = null then [GroupValue] else temp[GroupValue])

4) NewGroupValue

if Table.RowCount(Table.FindText([PreviousGroupValueTable],"N"))>0 and [GroupValue] = "Y" then "N" else [GroupValue]

4. remove four  columns: "GroupValue", "Level.1", "PreviousID", "PreviousGroupValueTable"

5. pivot coulmns "GroupName" and "NewGroupValue" as "Do not aggregate"

All the queries are here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJNzSsF0Qo4MEjOD4gjlWJ1opWMkHQElybh0mgE1eQH1mSMqskIj6ZIuCYTqCZcqmFui4S7zRRJB9FuM8PUBKRwaDdGsc8cVSs+byEC0AJTE2H7IE61xKcVwsJijgnCzbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, L1 = _t, L2 = _t, L3 = _t, L4 = _t, L5 = _t, L6 = _t, L7 = _t, Level = _t, group1 = _t, group2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"L1", type text}, {"L2", type text}, {"L3", type text}, {"L4", type text}, {"L5", type text}, {"L6", type text}, {"L7", type text}, {"Level", Int64.Type}, {"group1", type text}, {"group2", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "L1", "L2", "L3", "L4", "L5", "L6", "L7", "Level"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "GroupName"}, {"Value", "GroupValue"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Level.1", each Text.Combine(Record.ToList(let le = [Level]
in
Record.SelectFields(_,
    List.Select(
        Record.FieldNames(_), 
        each  List.Contains(
            List.Transform({1..le},each Text.From(_)),
           Text.Replace(_,"L","")
            )
        )
    )),",")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "PreviousID", each let currentLevel = [Level.1], le = [Level]
in
Table.Distinct(Table.SelectColumns(Table.SelectRows(#"Added Custom", each Text.StartsWith(currentLevel,[Level.1]) and le >= [Level]+1), {"ID"}))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "PreviousGroupValueTable", each let GN = [GroupName]
in
Table.AddColumn([PreviousID],"PreviousGroupVaule",
each
let i = [ID],
temp = 
Table.Max(Table.SelectRows(#"Added Custom1", each [ID] = i and GN = [GroupName]),"GroupValue")
in if temp = null then [GroupValue] else temp[GroupValue])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "NewGroupValue", each if Table.RowCount(Table.FindText([PreviousGroupValueTable],"N"))>0 and [GroupValue] = "Y" then "N" else [GroupValue]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"GroupValue", "Level.1", "PreviousID", "PreviousGroupValueTable"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[GroupName]), "GroupName", "NewGroupValue")
in
    #"Pivoted Column"

By the way, PBIX file as attached.

Best regards,

[Anonymous]

@v-lid-msft, this an excellent solution for the same set, and the result is exactly correct. I also appreciate that you broke down the logic. Unfortunately, additional rows and columns seem to exponentially slow the query down. With, e.g., 50 rows and 10 groups, the query processes 2 rows per second on my machine. An attempt on the full set of ~1500 rows and 50 groups made no progress. Is there a way to do this more efficiently? 

[v-lid-msft]

Hi @Anonymous ,

Could you please add step one by one and refresh one by one to find out until which step the query still run smoothly?

Best regards,

[Anonymous]

@v-lid-msft, the step that seems to be the issue is this one-- 

 #"Added Custom3" = Table.AddColumn(#"Added Custom2", "NewGroupValue", each if Table.RowCount(Table.FindText([PreviousGroupValueTable],"N"))>0 and [GroupValue] = "Y" then "N" else [GroupValue]),

I'm not sure why, the logic is sensible. This is pretty easy to test-- if you copy and paste a few more columns or rows, it'll get lethargic very quickly. 

[v-lid-msft]

Hi @Anonymous ,

Maybe the Table.FindText function is too heavy, Please try to change this step to following:

if Table.RowCount(Table.SelectRows([PreviousGroupValueTable],each [PreviousGroupVaule] = "N"))>0 and [GroupValue] = "Y" then "N" else [GroupValue]

By the way, PBIX file as attached.

Best regards,