Complex Table Flattening

Hi,

Thank you for your interest. I see I didn’t explain the details well enough.

For the output related to my first question, I have the following table in PowerBI, after I’ve merged the data:

Step 1) Ignore all rows with “Signals.In Scope” = False

                I’ve got this solved

Step 2) If Sources.EndPoint and Destinations.Endpoint are both TRUE, then no need for further processing, just bring this row into the output table.

USB example:

Nothing to do here, just bring to the output:

Step 3) If Destinations.Endpoint is False, then we want to “find the path” to the destination that is an endpoint.

So we “Traverse” all rows to “trace” the paths of a signal (in this case FTP), going from rows where Signals.Signal Name is the same, to find the row in the table where that row’s Source Name = this row’s Destination Name. Continue row-by-row, matching to until we find a row where Desinations.Endpoint = TRUE.

FTP Example:

• Row 1, Source is True, so we use “Computer 1” as the source, but the destination “Router A” is not an Endpoint
• So we find another row in the table where the signal name is “File Transfer Protocol (FTP)” and the source of “Router A”; row 2 in this case matches
• We see in row 2 that the destination.endpoint is False for “Router B”, so we again find a new row where the signal is “File Transfer Protocol (FTP)” and has a source of “Router B”
• Now we find row 3, row 3 has a distination.endpoint of TRUE, so we know we’ve found the end of the trace and we use the Destination Name “Computer 3” from this row as the new endpoint for the output.

From this we output a row of source “Computer 1” to destination “Computer 3” with the FTP signal

Step 4) We continue this trace to ensure we’ve identified all paths of the signal

Web Traffic Example:

• From the table, we can see rows 7 and 8 both are “Web Traffic (HTTP)” signals with TRUE endpoints for “Computer 3” and “Computer 4”
• So the above “Step 3” formula should be repeated in such a way to trace each path from “Web Server 1” to these two destinations
• Output should have 2 rows in this case, as follows:

So my final output, after filtering and tracing should look like:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

For my question 2, I want to be able to “group” and produce a “sort order” of the output table from above in Question 1, with an “operational exchanges” table. So any and all signals that match the Operational Exchange should be grouped, and the sort order is determined by source to destination pairing.

“Critical File Transfer” example:

• The “Critical File Transfer” operational exchange is a grouping of all exchanges from “Computer 1” to “Computer 5”
• We can see Row 1 of the output from question 1 has a source of “Computer 1”
• Row 2 has a destination of “Computer 5”
• Row 1 destination matches the source of Row 2, we’ve identified our path and the “sort order” for the path
• Output should look like: