Help with database structure OR relationship schema!

[JohnM1985]

Hi,

I'm attempting to create a repository of all my teams Power Bi reports, showing all of the datasources, and all of the notebooks which are used to populate these data sources.   Part of my challenge is that there are multiple datasources used in PBI reports, and each notebook can output to multiple datasources.

On top of that, I don't want to build a complete database (i.e. with a new table illustrating the datasources for Report1, Report2, and the same for tables which make up Datasource 1, 2 etc) and it needs to be simple to update for the user, so I'm currently separating multiple values in the same column, using a comma.

Currently I have 

Table1:

ReportName	DataSource
Rep1	DS1, DS2, DS3
Rep2	DS4
Rep3	DS1, DS5

Table2:

DataSource Name	Related Tables
DS1	T1, T2, T3
DS2	T4
DS3	T1
DS4	T4
DS5	T1, T2, T3, T4

Table 3:

Notebook Name	TablesUpstream	TablesDownstream
Nb1	T1, T2	T3
Nb2	T4, T1	T3
Nb3	T1, T2, T3	T1

I was hoping that I could use Table1 (for example) and then duplicate this table, split values by delimiter and then build a relationship to this table [that works] and then connect this in a similar manner to the notebooks - in the end giving me a nice matrix/table visual where a user could search through the list of reports, and quickly see details about that report, which datasources it connects to, and then which notebooks they might need to look at to adjust any final output.

Perhaps I'm trying to find a simple solution to a complex problem here, but it feels like it should be possible!!! (-:

[amitchandak]

@JohnM1985 , I think split by delimiter into rows, is the best solution for this problem for table 1 and Table2, and if needed for Table 3 too

Power Query Split Column , Split Column By Delimiter: https://youtu.be/FyO9Vmhcfag

[JohnM1985]

Thanks @amitchandak ,

The problem that this seems to bring is the relationships between the tables, as table 1 goes from:

ReportName	DataSource
Rep1	DS1, DS2, DS3
Rep2	DS4
Rep3	DS1, DS5

To:

ReportName	DataSource
Rep1	DS1
Rep1	DS2
Rep1	DS3
Rep2	DS4
Rep3	DS1
Rep3	DS5

But I don't want that to display with multiple names (i.e. Rep1's) to the user - so I can build a relationship from my original table, to the delimited one, but then to connect this to the next tables I end up with a many-many relationship which I'm trying to avoid, or by using bridging tables with 1 to 1 relationships, which doesn't seem to work.... I wondered if there was a more creative way to get to my solution