Import query from Oracle based on the data from extarnal table

[abde139]

I am new to power BI. Sorry if my question may be trivial.

I have data imported from azure cloud which contains about 1 000 lignes.

Id_cloud	score
1	0.8
2	0.3

I also have data from Oracle database that contains about 100 000 000 lignes.

Id_oracle	name	email	phone
1	name1	name1@mail.com	0123456
2	name2	name2@mail.com	0123457
3	name3	name3@mail.com	0123458

I am trying to join the two tables based on ID in power BI. However, using the import query to import 100M lignes from oracle is too heavy and taking too much time, since we want only 1 000 data from it. And I could not find a way for the selected query to only include the ID in the external table ( cloud table).

Is there any way to select only selected ID from oracle database that much the ID in the cloud table already imported.

Many thanks in advance for your answer.

[mh2587]

1. Load both Data Sources:

   • Load the data from your Azure Cloud source.
   • Load the data from your Oracle database source.

2. Filter the Oracle Data:

   • In Power Query, apply a filter to the Oracle data source to only include the rows with IDs that match the ones in your Azure Cloud table. You can use the "Filter Rows" transformation to achieve this.

3. Merge the Tables:

   • After filtering the Oracle data, you can merge or join the two tables in Power Query using the "Merge Queries" option. Here's how:

     a. Select your Azure Cloud table in the Power Query window.

     b. In the "Home" tab, click on "Merge Queries."

     c. Choose the Oracle data source as the second table to merge.

     d. Specify the join condition, which is matching the "ID_cloud" column from the Azure Cloud table with the filtered "ID" column from the Oracle table.

     e. Select the type of join you want (e.g., Inner Join, Left Outer Join, etc.).

     f. Click "OK" to perform the merge.

   • This will create a new table in Power Query that combines data from both sources based on the specified join condition.

4. Expand the Merged Table:

   • After merging the tables, you may need to expand the columns from the Oracle table that you want to include in your final dataset. Select the columns you want to keep and click "OK."

5. Load the Merged Data:

   • Once you have the merged and expanded table, you can load it into your Power BI model.

[abde139]

Thanks @mh2587  for your efforts and detailed answer. I just have a concern with the 2nd step: "Filter the Oracle Data" . It indicates the use of the "Filter Rows" transformation, does it means I should select manually all the required ID from Cloud table and filter the Oracle table ?

[mh2587]

I think first merge it and filter the data if any blanks occure

[abde139]

Thank you @mh2587, in fact to perform the merge, the data should be uploaded first and that is the problem, it is too heavy to upload 100M lignes and then perform the necessary treatement. Is there a way to upload just the necessary lignes ( using SQL query while importing  for example ) ?