Limited load from data souce

[MarieD]

Hi all,

I'm new to PowerBI and want to make reports from a Magento Database. the connection has already been set up by using Mysql connection, but the problem is that there are a lot of rows in the database and PowerBI Desktop has problems with reloading and refreshing.

Now as I'm still working on my model and visuals, i want Power BI to load only top 1.000 rows from the database. Is there a way this can be done? I've read topis about adding a SELECT statement, but I;m now sure how and where i must add this? It looks now that is is filtered by date but even then there are over 1.000.000 rows.

This is the first part of the query:

let
Source = MySQL.Database("00.000.000.00:0000", "XXXX_m2_production", [ReturnSingleDatabase=true]),
XXX_m2_production_sales_order_item = Source{[Schema="XXXX_m2_production",Item="sales_order_item"]}[Data],
#"Filtered Rows" = Table.SelectRows(XXXX_m2_production_sales_order_item, each [created_at] >= #datetime(2019, 1, 1, 0, 0, 0)),

It would be great if somebody can help me with this!

[v-alq-msft]

Hi, @MarieD 

I'd like to suggest you use 'Filter rows' feature to filter out the data youwant. Or you can do the filter when you connect to the database. I created data to reproduce your scenario.

Testalq:

You may use filter rows to filter out the result as below.

Then you can right-click the step 'Filter Rows', click 'View native query', copy the codes.

Then you can create a new query, click 'Advanced options', paste the codes in 'SQL statement'.

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MarieD]

Many thanks for your help!! 

[mahoneypat · Accepted solution]

You could do it with an SQL statement but it is easier to use Keep Rows in the query editor.  In your query, click on your 2nd step or on the Filtered Rows step, and on the Home tab click on KeepRows/Keep Top Rows.  Put in 1000 in the popup and it should add a Keep Rows step.  That way, query folding should still occur.

When you are done with your query building, you can just remove that step to get all the data when you need that.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[MarieD]

Thank you Pat!