Specific number of latest rows from SQL Server with Power Query

[Anonymous]

Being relatively new in Power BI Desktop I have the following scenario from a client:

We have some sensors that send data to an Azure SQL Database constantly. About 300 rows added in the database per day.

All the data are displayed in a report through Direct Query and page autorefresh. For the moment a particular visual (Line Chart), displays ALL the data from the database.

I want to have after each refresh the last 500 rows of the database.

Is this possible?

Thanks in advance

[Anonymous]

@goncalogeraldes 

Thanks for your immediate answer, but the 'keep rows' function, as Power Query message displays, is not supported in imported data through DirectQuery.

[goncalogeraldes · Accepted solution]

My bad, didn't read your question thoroughly!! Are you using a SQL Statement to query the source database? If so, you can try the following in your SQL Statement:

let
    Source = Sql.Database(Server, Database, [Query="SELECT TOP (500) Columns FROM Table ORDER BY DateKey DESC"])
in
    Source

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[Anonymous]

@goncalogeraldes 

Where should I put this snippet? I'm rather beginner... 😟

[goncalogeraldes]

Go to the "Transform Data" option in the Home tab and in the new window select the table you need to query. After this step you need to open the "Advanced Editor" and paste the code.

Please be aware that, if you have applied transformations to your data in Power Query, the code I've provided will discard those changes. You need to adapt the code to your requirements, namely the Server and Database names and the Column and Table names on the Query!

[goncalogeraldes]

Hello there @Anonymous ! You can go to Power Query ("Transform Data") and choose the "Keep Rows" options in the "Home" tab and then the "Keep Bottom Rows".

In the pop-up window just insert the number of rows you want to keep, in your case 500. Click "Ok" and then "Close & Apply" and you are good to go!

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes