Complex Table Flattening

[talvord]

Hi,

First time poster; I’m stuck on (what I think) is an advanced problem. I’ve tried searching for my problem and haven’t found an answer. I might not even be using the right search term(s).

Background:

I have model of a computer network that consists of nodes, signals (data transfers) and operational exchanges. A visual of a simple example:

Each node (a computer, router or server in this example) has three attributes, a unique identifier, a name, and a Boolean marking the node as an endpoint or not. Example below:

Each signal has three attributes similar to nodes: ID, name and Boolean indicating if the signal is current in scope for analysis. Example:

All signal exchanges are reported on a “per hop” basis. Example exchanges below:

I’ve got all the tables and relationships working properly in PowerBI generating the list of exchanges:

Ok, end of background, onto my questions.

Question 1

The first output I’m looking to build, is a table of all the data exchanges, where the exchanges have been “flattened” or “traversed”; that is to say, all the rows that have a destination that is an endpoint, but the source of that row is not an endpoint. The table continues to get analyzed for more rows that have a matching signal until a source and signal pair can be found and originate from an endpoint. Also, signals that are not in scope get ignored.

For example, the File Transfer Protocol signal starts at Computer 1 and ultimately ends at Computer 3. Also, Web Traffic Starts at Web Server 1 and ends at both Computer 3 and Computer 4. The USB file transfer is already “flatted” correctly.

The output should look like this:

I’ve tried pivoting the table, using the DAX PATH function, and read through a handful of questions/articles here and on Reddit. I don’t even know what to properly call the above analytical flattening of my table. Any help or ideas?

--

Question 2

Second, next output I’m looking for, once I have the above table, is to “group” these data exchanges by a human defined set of “operational exchanges”. An operational exchange is defined by a name, source and destination. Example below:

These Operational Exchanges are defined prior to network design; therefore the underlying network and signal types can change. So, in our example, the data flow for “Critical File Transfer” is FTP Computer 1 to Computer 3, and then USB from Computer 3 to Computer 5. What the data flow table should look like based on this example should look like this:

Any help or ideas?

Examples of my Excel tables and PowerPI file can be found here: https://github.com/alvordtimothy/powerbi/tree/main/Network%20Example

Thanks!

[PwerQueryKees]

I could probably solve this in PowerQuery M language, but I suspect it would involve recursive function calls resulting in complex logic if you are not too familiar with M language and I don't know what volumes we are talking about, but it might be slow on high data volumes. Would you even be interested in (help with) a M language solution? I am not committing to any work here, but if you aren't interested, I will leave it here...

[8x88pro]

Thank for sharing!

[talvord]

Hi,

Anyone have an idea where I can start? I've been reading many BLOG posts on List.Generate, GroupBy, Summerize, and other. I don't seem to be moving the right direction with all the attempts with these funtions.

I think the term that describes what I'm looking to do is finding a path via arcs in a direct graph. (Graph meaning the data model graph, not visual graphs).

Thanks

[Anonymous]

Hi @talvord ,
Based on your description, I understand that you want to create a chart to show the path of events. Can you elaborate on your logic for getting these paths? Preferably for the filtering criteria to be explained.

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[talvord]

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: