I need to create a custom matrix or table visual. I'm trying to build a predecessor-successor report from querying data out of P6. I have two tables, one lists all of my activity ID's, as well as all of the information I need about each activity, and it only lists every activity from the schedule one time. The other table only has two columns that I need, "predecessor activity ID's" and "Successor activity ID's". It lists every ID multiple times for each column, it lists an activity id in either the pred or succ column and then lists one of its pred/succ's in the other column of that row, if an activity has multiple logic ties then it lists that ID multiple times in whichever column as necessary.
I need to use this information to create a report that lists the "selected" activity ID in the middle row of the visual, with all of its predecessor's in rows above it, and all of its successor's in the rows below it.
For example, this is what I want the end result of the visual to look like:
| Pred1 Act ID | Pred1 Act Name | Relationship type | Pred1 Act Start (date) | Pred1 Act End (date) | Lag | Total float |
| Pred2 Act ID | Pred2 Act Name | Relationship type | Pred2 Act Start (date) | Pred2 Act End (date) | Lag | Total float |
| Main Act ID | Main Act Name | This column blank | Main Act Start (date) | Main Act End (date) | Lag | Total float |
| Succ1 Act ID | Succ1 Act Name | Relationship type | Succ1 Act Start (date) | Succ1 Act End (date) | Lag | Total float |
_________________________________________________________________________________________________________________________
| Pred1 Act ID | Pred1 Act Name | Relationship type | Pred1 Act Start (date) | Pred1 Act End (date) | Lag | Total float |
| Main Act ID | Main Act Name | This column blank | Main Act Start (date) | Main Act End (date) | Lag | Total float |
| Succ1 Act ID | Succ1 Act Name | Relationship type | Succ1 Act Start (date) | Succ1 Act End (date) | Lag | Total float |
| Succ2 Act ID | Succ2 Act Name | Relationship type | Succ2 Act Start (date) | Succ2 Act End (date) | Lag | Total float |
_________________________________________________________________________________________________________________________
etc.
I've been trying to figure this out for about two weeks now with no luck. I'm considering learning Python to see if I can write code for a custom Python visual in Power BI to get this to work. Any help would be much appreciated.
(Note: All of the information I need to create the visuals exists already in the two tables I mentioned)
