Forum Discussion

abde139's avatar
abde139
Frequent Visitor
2 years ago

Import query from Oracle based on the data from extarnal table

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_cloudscore
10.8
20.3

 

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

 

Id_oraclenameemailphone
1name1[email protected]0123456
2name2[email protected]0123457
3name3[email protected]

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.

Need Help

4 Replies

  • mh2587's avatar
    mh2587
    Super User
    2 years ago

    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's avatar
      abde139
      Frequent Visitor
      2 years ago

      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's avatar
        mh2587
        Super User
        2 years ago

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