Table matrix grouping and transform

[alexdi]

I have an permissions matrix that looks like this:

ID	item	group1	group2	group3
1	string_b	Y	N	N
2	string_x	N	Y	Y
3	string_e	Y		Y
4	string_b	N	Y	Y
5	string_x	N	Y	N
6	string_b	Y	Y	Y
7	string_e	Y	N	Y

Groups associate to items. However, the items are not always uniquely-named, and I need to know when there's a permissions mismatch where the item is enabled for one row, but disabled on another. 

Adjacent to the table above, I have another column that looks like this:

ID	Disabled elsewhere
1	group1
2	group3
3
4	group2, group3
5
6	group1, group2, group3
7

It's driven by about 50 helper columns, which, because of the dynamic nature of the groups, need to use elaborate formulas with lots of indirects, which make the sheet unusable. I need to convert it to a mechanism I can run on command. DAX is definitely preferable to VBA here. 

Can anyone suggest how to transform the table to create the result above? I've been hacking away at it and not making much progress. Much appreciated as always, a lot of the functionality of this matrix derives from advice from this forum.

A few notes:

1500 rows
Up to 50 groups
Unique IDs, sequential
Unique group names, dynamic number of groups
Item strings are not unique
Name/group associations can be "Y", "N", or null

[ImkeF]

Hi @alexdi 

please paste the following code into the advanced editor and follow the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSouKcrMS49PAjIjgdgPjGN1opWMEJIVUIlIMAZJGiMkU6EScDkTVFNRNZpiNxVipRmmexA6zTGthOiOjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, item = _t, group1 = _t, group2 = _t, group3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"item", type text}, {"group1", type text}, {"group2", type text}, {"group3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "item"}, "Attribute", "Value"),
    GroupOnItemAndGroup = Table.Group(#"Unpivoted Other Columns", {"item", "Attribute"}, {{"All Rows", each _, type table [ID=number, item=text, Attribute=text, Value=text]}}),
    CountDistinctNumberOfPermissions = Table.AddColumn(GroupOnItemAndGroup, "DistinctValues", each List.Distinct([All Rows][Value])),
    MismatchIfMoreThanOnePermission = Table.AddColumn(CountDistinctNumberOfPermissions, "Mismatch", each if List.Count([DistinctValues]) > 1 then [Attribute] else null),
    Cleanup = Table.SelectColumns(MismatchIfMoreThanOnePermission,{"All Rows", "Mismatch"}),
    ExpandIDs = Table.ExpandTableColumn(Cleanup, "All Rows", {"ID"}, {"ID"}),
    GroupOnIDs = Table.Group(ExpandIDs, {"ID"}, {{"All Rows", each _, type table [ID=number, Mismatch=text]}}),
    CombineMismatchedGroups = Table.AddColumn(GroupOnIDs, "Custom", each Text.Combine([All Rows][Mismatch], ", ")),
    #"Sorted Rows" = Table.Sort(CombineMismatchedGroups,{{"ID", Order.Ascending}})
in
    #"Sorted Rows"

I've named the steps according to a mismatch logic that is plausible to me.

But as that doesn't seem to match yours, I hope this gives you an idea where your explanation of the desired mismatch-logic needs more clarification.

[alexdi]

ImkeF, that's a really terrific general solution, thank for that. It's 95% there for this problem. The reason I'm not looking to return all mismatches (just those where the test value is Y, and a row with the same item is N) is because, for this system, the N's win. If the test row is N and there's a Y elsewhere, that doesn't require action because the mismatch doesn't matter. If it's Y and elsewhere is N, then you're not getting the Y you wanted, so you need to go fix the other row. 

[ImkeF · Accepted solution]

OK, so this should do then:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSouKcrMS49PAjIjgdgPjGN1opWMEJIVUIlIMAZJGiMkU6EScDkTVFNRNZpiNxVipRmmexA6zTGthOiOjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, item = _t, group1 = _t, group2 = _t, group3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"item", type text}, {"group1", type text}, {"group2", type text}, {"group3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "item"}, "Attribute", "Value"),
    GroupOnItemAndGroup = Table.Group(#"Unpivoted Other Columns", {"item", "Attribute"}, {{"All Rows", each _, type table [ID=number, item=text, Attribute=text, Value=text]}}),
    AddIDs = Table.AddColumn(GroupOnItemAndGroup, "ID", each [All Rows][ID]),
    #"Expanded ID" = Table.ExpandListColumn(AddIDs, "ID"),
    GetTestValue = Table.AddColumn(#"Expanded ID", "Test", each [All Rows]{[ID=[ID]]}[Value]),
    GetElsewheres = Table.AddColumn(GetTestValue, "elsewhere", each Table.SelectRows([All Rows], (x) => x[ID] <> [ID])[Value]),
    CheckForMismatch = Table.AddColumn(GetElsewheres, "Mismatch", each if [Test] = "Y" and List.ContainsAny([elsewhere], {"N", ""}) then [Attribute] else null),
    #"Grouped Rows" = Table.Group(CheckForMismatch, {"ID"}, {{"All Rows", each _, type table [item=text, Attribute=text, All Rows=table, ID=number, Test=text, elsewhere=list, Mismatch=text]}}),
    ExtractMismatch = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([All Rows][Mismatch], ", ")),
    #"Sorted Rows" = Table.Sort(ExtractMismatch,{{"ID", Order.Ascending}})
in
    #"Sorted Rows"

[alexdi]

That's awesome, thanks so much! That just shaved two seconds of calculation delay from every interaction.

If you're game for another challenge, there's one more difficult calculated column I posted here:

https://community.powerbi.com/t5/Power-Query/Determining-if-a-parent-in-a-hierarchy-is-disabled-for-...