Forum Discussion

Venkat_Thota's avatar
Venkat_Thota
Frequent Visitor
1 year ago
Solved

How to Limit rows from Oracle source

I am connecting to oracle data source using a query.  select col1, col2, col3....colN from Table group by col1,col2,col3...colN I am adding athe end 'fetch first 5000 rows only' to limit no of rows...
Need Help
  • tharunkumarRTK's avatar
    tharunkumarRTK
    1 year ago

    Venkat_Thota 

    You can follow this synatx then:

    let
DEVQUery = "select * from some_table fetch first" &  parameterName  &"rows only",
PRODQuery = "select * from some_table",
Query = if parameterName = 5000 then DEVQUery else PRODQuery,
Source = Oracle.Database(Server, [Query=Query])
in 
Source

    From service you can change the parameter value to anything but 5000. then you will be able to to do the full load.

    please be informed you need to keep the parameter data type as whole number. 

     

    Need a Power BI Consultation? Hire me on Upwork

     

     

     

    Connect on LinkedIn

     

     

     








    Did I answer your question? Mark my post as a solution!
    If I helped you, click on the Thumbs Up to give Kudos.

    Proud to be a Super User!

     

tharunkumarRTK's avatar
tharunkumarRTK
Super User
1 year ago

Venkat_Thota 

I think query folding is not happening in your case. Instead of using 'Keep Top Rows' transformation, Use native query option. 

 

Step 1: Create a parameter and assign the value as 10,000. (Do not forget to select the data type as whole number)

Step 2: Connect to your oracle database and in the advanced tab, put your SQL query and hard code the row limit 10,000.

select * from some_table fetch first 10000 rows only

Step 3: Open advanced edit advanced editor and replace 10000 with the parameter name. After the modification your m code should look similar to 

let
Source = Oracle.Database(Server, [Query="select * from some_table fetch first" &  parameterName  &"rows only"])
in 
Source

 

Need a Power BI Consultation? Hire me on Upwork

 

 

 

Connect on LinkedIn

 

 

 








Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!