Forum Discussion
Table matrix grouping and transform
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...
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"
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.
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"
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: