List of all entries in a Text column that contains a value present in the same column

[Anonymous]

USER	GROUP URL
1	ABC/DEF
1	PQR/STU
2	ABC/DEF/GHI/LMN
3	ABC/DEF/GHI
4	PQR/STU/VWX
5	MNO/CDE/TUV

So, if USER 1 has access to a path - ABC/DEF then he must have access to every group that falls under it as well. Hence, I want to find a way to display all the levels of access a user has by inheritance using Power Query.

Example, for User 1 here, the solution should be a list/record or just an indicator column that helps identify all the groups a user has access to - 

ABC/DEF, ABC/DEF/GHI, ABC/DEF/GHI/LMN, PQR/STU, PQR/STU/VWX

Please note that it has not been explicitly mentioned that User 1 has access to aforementioned sub-groups. 

[ImkeF · Accepted solution]

Hi @Anonymous ,
please find a possible solution in the file attached.
For performance reasons, the solution splits up the combinations value into rows and performs one merge before grouping things back together:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0ctZ3cXVTitWB8AMCg/SDQ0LBfCOEvL67h6e+j68fWNwYVRwsZoLQqx8WHgEWMwWK+fr56zu7uOqHhIYpxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [USER = _t, #"GROUP URL" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"USER", Int64.Type}, {"GROUP URL", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Index", "GROUP URL", "GROUP URL - Copy"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated Column", {{"GROUP URL - Copy", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "GROUP URL - Copy"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"GROUP URL - Copy", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"GROUP URL - Copy"}, #"Changed Type1", {"GROUP URL - Copy"}, "Matches", JoinKind.LeftOuter),
    #"Grouped Rows" = Table.Group(#"Merged Queries", {"USER"}, {{"Groups", each Table.Distinct(Table.Combine(_[Matches]), "GROUP URL")}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([Groups][GROUP URL], ", "))
in
    #"Added Custom"