filter and create relationships before loading data

[Rjesak]

I think this is a very basic question but I've been searching and not found an answer so I'm going to try.

We have two rather large tables that are connected by a 1:many relationship through a unique sequence number. I wrote a SQL query to select the filtered set of the parent table along with the related supporting records but that creates a single really large table with lots of duplicate data.

I'd rather build this as a model (parent table with a relation to the supporting table) but if I don't filter the supporting table based on the connection to the parent table, it's too large.

I know I can do this but I can't for the life of me figure out how.

[AiolosZhao · Accepted solution]

Hi @Rjesak ,

Do you have mean you want to create two tables in power bi finally?

The problem is that you want to limit the parent table and limit the supported table(based on parent table), right?

And your query get one table with all duplicated records after using the "parent" limitation.

If yes, I think you can create the parent table by:

select xxx

from parent_table

where limitation

and for support table:

select xx

from support table

inner join parent_table

where parent_limitation

If I'm wrong, please show me what you want more clearly, if you have sample data that will be better.

Thanks.

Aiolos Zhao