Forum Discussion
Create bridge table with distinct IDs and duplicate values for RLS
Hey!
I have five tables in my report (TBL1, TBL2, TBL3, TBL4, and MainTable) and the only thing that changes is the data in each table (since each table is from a different area). All tables follows a similar structure like this:
| ID | AREA | SECTOR | VALUE |
| 1 | Hyho | SP-1 | 235 |
| 2 | Coex | Tek | 22 |
| 3 | Comali | OPL1 | 87 |
| 4 | Kioi | SP-1 | 54 |
| 5 | Abeg | Tek | 90 |
I have a main table, which contains all the data from all the other tables combined, called MainTable. I want to relate the MainTable to all the other four tables by ID, so that there is no many-to-many relationship between them. Therefore, I want to create a bridge table that contains only the distinct IDs of all the tables in my report, so that TBL1, TBL2, TBL3 and Main Table relate to this bridge table.
In addition to this distinct ID column, I want this bridge table to contain a second column that represents the join of all the Sectors from each of the tables. This column doesn't need to have only distinct values, as I will use it in the RLS.
The expected bridge table would look like this:
| ID | SECTOR |
| 1 | SP-1 |
| 2 | Tek |
| 3 | OPL1 |
| 4 | SP-1 |
| 5 | Tek |
How can I create this bridge table with these two columns?
Hello nok
To automate this in Power Query, you may also use below M codes in a blank query (please adjust the your table names for the Source)
let Source = { TBL1, TBL2, MainTable }, // Rename your tables for the source Combined = Table.Combine( List.Transform( Source, each Table.SelectColumns(_, {"ID", "SECTOR"}) ) ), DistinctPairs = Table.Distinct(Combined) in DistinctPairsYou will get below Bridge table in Power Query
and then build your relationship in Power BI.
Hope this helps:)
9 Replies
nok Can you provide a sample file or at least sample data for this?
Greg_Deckler , Hi!
I edited the question, inserting sample data, a result of what the final bridge table should look like and also a model of what I want to do with the relationship between these tables.
Hello nok
To automate this in Power Query, you may also use below M codes in a blank query (please adjust the your table names for the Source)
let Source = { TBL1, TBL2, MainTable }, // Rename your tables for the source Combined = Table.Combine( List.Transform( Source, each Table.SelectColumns(_, {"ID", "SECTOR"}) ) ), DistinctPairs = Table.Distinct(Combined) in DistinctPairsYou will get below Bridge table in Power Query
and then build your relationship in Power BI.
Hope this helps:)
Hi nok
You can append table1....tablex and delete the column accept ID and remove duplicates.
then you will have a bridge table ready where you can use it to build relationshipsnok In DAX that would be:
New Table = DISTINCT( SELECTCOLUMNS( 'Table', "ID", [ID], "SECTOR", [SECTOR] ) )Hi nok ,
Thank you for reaching out to Microsoft Fabric Community.
Thank you Greg_Deckler swathigouda MasonMA Jaraxus for the prompt response.
I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.
Thank you.
