Delete unrelated data

[kevlarmpowered]

Is there a way to delete unrelated data based on relationships?  For example, tableA comes from datasourceA.  TableB comes from datasourceB, but it has many records which are unrelated to tableA (the parent table).  Is there a way to easily delete the rows from tableB where there is no matching ID in tableA?

TableA contains all the IDs, TableB contains all the IDs in TableA (and some extra unnecessary and unwanted rows).  I figured if I deleted the unrelated rows in TabelB, I could shrink my file size because there are a bunch of unnecessary rows.  

[v-shex-msft]

Hi @kevlarmpowered,

For your requirement, I think you can also use advanced t-sql query to handling these unrelated records.

Reference links:

Selecting data from two different servers in SQL Server

Regards,

Xiaoxin Sheng

[kevlarmpowered]

Linked servers are not really an option... 

The solution of joining the tables within PowerBI works, with the added headache that it had to download the parent/related table each time you try to refresh the table in question.  So if you try to refresh TableB, it automatically includes TableA which means moving lots of data that may not have updated.

[v-shex-msft]

HI @kevlarmpowered,

Maybe you can write sub query to find out unrelated records id, then use these list as filter parameter to remove related records.

But I'm not so sure how to direct invoke those tables without link table, maybe you can try to create dataview based on other datasource, then operation on these views.

Regards,

Xiaoxin Sheng

[Vvelarde]

@kevlarmpowered

Hi, In Query Editor. Merge Table B with Table A using the ID Column. Expand it (Select to expand only the ID )and Filter Unchecking the Null in this new Column.

Let me know if need more help

Regards

Victor

Lima - Peru