Create bridge table with distinct IDs and duplicate values for RLS

[nok]

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?

[v-venuppu]

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.

[Greg_Deckler]

@nok In DAX that would be: 

New Table = DISTINCT( SELECTCOLUMNS( 'Table', "ID", [ID], "SECTOR", [SECTOR] ) )

[swathigouda]

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 relationships

[MasonMA]

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
    DistinctPairs

 You will get below Bridge table in Power Query 

and then build your relationship in Power BI. 

Hope this helps:) 

[Jaraxus]

@nok You can do it in Power Query. Append queries as a new (TBL1-TBLX), then select only needed columns and remove duplicates.

[Greg_Deckler]

@nok Can you provide a sample file or at least sample data for this?

[nok]

@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.