Using Excel to separate complex many-to-many relationships

Question

I have a set of data in excel that follows the below format: Table displaying data and its attributes/relationships Image displaying relationships between High, Medium and Low level attributes

I need help finding a way to display this information in:

1. A way that is semi-interactive within the excel workbook that the raw data sits in (think something like pivot table, using the slicer as a clickable filter)
2. Easy/quick to update whenever a change is made to the raw data table. (there are over 350 applications, each with their own application names and various High/Medium/Low Level attributes)

One solution that I have found (but is wildly inefficient and tedious) is manually creating a new row for each Low Level Attribute and matching it up 1 by 1 to the relevant Medium level attribute, High Level attribute and Application Name. However, this has to be done in a separate worksheet not linked to the current worksheet, and with how many Low Level attributes there are that are spread across all 360 applications, it is simply not possible in a feasible timescale. An example of this solution is below (all done manually):

2 Tables demonstrating the manual separation solution

I have tried using Power Query to separate and create a new row manually using the comma as a delimiter, however it is ineffectual on Applications where there are multiple high/medium/low level attributes, as it will (incorrectly) copy the information across multiple rows.

Any suggestions/help would be greatly appreciated thanks :)

Answer 1

You can do this in Power Query using helper columns and a filter

See the code comments and follow the applied steps to understand the algorithm.

The code is "hard-coded" for just three levels of attributes, with the names you show.

If there should be a variable number of sub-attributes, the code could be changed to accommodate using List.Accumulate or List.Generate which would allow looping.

Original Data

M Code

let

//Change next line to reflect data source
    Source = Excel.CurrentWorkbook(){[Name="Table29"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,
        List.Transform(Table.ColumnNames(Source), each {_, type text})),

//split the columns by delimiters into rows
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"High Level Attribute/s", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "High Level Attribute/s"),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter", {{"Medium Level Attribute/s", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Medium Level Attribute/s"),
    #"Split Column by Delimiter2" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter1", {{"Low Level Attribute/s", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Low Level Attribute/s"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter2",{{"High Level Attribute/s", Text.Trim, type text}, {"Medium Level Attribute/s", Text.Trim, type text}, {"Low Level Attribute/s", Text.Trim, type text}}),

//Add "grouper columns"
    #"Add Med Grouper" = Table.AddColumn(#"Trimmed Text", "Medium Grouper", each Text.BeforeDelimiter([#"Medium Level Attribute/s"],".",0), type text),
    #"Add Low Grouper" = Table.AddColumn(#"Add Med Grouper", "Low Grouper", each Text.BeforeDelimiter([#"Low Level Attribute/s"],".",1), type text),

//Filter the table based on equality between attribute and next lower lever grouper column
    filtered = Table.SelectRows(#"Add Low Grouper", each 
            [#"High Level Attribute/s"] = [Medium Grouper]
            and [#"Medium Level Attribute/s"] = [Low Grouper]),
            
    #"Removed Columns" = Table.RemoveColumns(filtered,{"Medium Grouper", "Low Grouper"})
in
    #"Removed Columns"

Results

Answer 2

Power Query has a "Split Columns" feature that can be set on a delimiter (such as a comma), and then split into a separate row for each comma (with the other columns retaining the same information). That should separate out the rows for you based on each attribute, then you can create a pivot table based off the new data with the separated out rows. This means you can retain the interactivity whilst also being to update the information quickly.

Answer 3

How about this approach using formulas (Office 365):

=IFNA(TEXTAFTER(TEXTBEFORE("."&TEXTSPLIT(E4,,", ")&".",".",SEQUENCE(,4)),"."),B4)

Or for multiple cells:

=LET(range,B4:E5,
DROP(REDUCE(0,SEQUENCE(ROWS(range)),LAMBDA(x,y,VSTACK(x,IFNA(TEXTAFTER(TEXTBEFORE("."&TEXTSPLIT(INDEX(TAKE(range,,-1),y),,", ")&".",".",SEQUENCE(,4)),"."),INDEX(TAKE(range,,1),y))))),1))