How to select only the top 1000 rows when importing from SQL to power bi?

user_7075138 0
Viewed 7171

I am trying to import data from a SQL server into power BI. There is a section on the advanced options called SQL statement.

I know that the SQL statement for what I require is:

Select TOP 1000 * from [Table]

How do I write this in the Power Bi at the time of data source / import. So that it runs this statement for each of the tables I plan to import?

powerbi sql-server sql
2 Answers

You can try this at the time of importing SQL Server data.

enter image description here

After loading data you can keep and remove rows using keep rows as shown below

enter image description here

enter image description here

user_105325000

If all the tables you want are on the same database, then you can navigate to that database as the first step in your query.

Database

From there, filter down to select just the tables you want.

Filter Rows

(You can see the preview of the cell selected in the bottom pane.)

Now that you've got the tables you want, you can apply a TopN function to the entire column (I chose top 3).

Table.TransformColumns(#"Filtered Rows",{{"Data", each Table.FirstN(_,3), type table}})

A quick way to add this step is to do a transformation on a text column and then just replace the column and the function applied. For example, if you format the Schema column to UPPERCASE using the GUI, it will add the step

Table.TransformColumns(#"Filtered Rows",{{"Schema", Text.Upper, type text}})

from which you can swap out the column, function, and type for what you actually want (see previous).

Top 3

At this point, your tables are all trimmed to the top N rows and you can load each one to its own query by right-clicking on the table cell and choosing "Add as New Query". Alternatively, you can right-click on the Database query in the left pane (see the first image) and choose "Reference". This creates a new query from which you can simply click on the Table you want and it will return just that one.

Note: The former method will automatically name the new query after the table you expanded but the latter would work better if you wanted to change your N value since it doesn't recreate the whole query.

Either way, if you right-click on the last applied step in each of these new tables, you can choose "View Native Query" and you can see that the statement passed back to the server is a simple select top 3.

select top 3
    [$Table].[DealSpecificKey] as [DealSpecificKey],
    [$Table].[DateInvestment] as [DateInvestment],
    [$Table].[DateInvestmentKey] as [DateInvestmentKey],
    [$Table].[DateRedemption] as [DateRedemption],
    [$Table].[DateRedemptionKey] as [DateRedemptionKey]
from [dbo].[AuxDaysInvested] as [$Table]
user_7652260
Related