Retrieve certain number of rows per category

[Anonymous]

Hello all!

I have a data source that's a MySQL DB. Within that is a huge table (+10million rows) which I don't wish to refresh in its entirety everytime.

Keeping the first rows is simple, but how do I keep the first rows for each distinct category in the table?

In my case the data is identified by customer name, so how would I got about writing a query to retrieve the first 10,000 rows for each customer?

Thanks!

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Do you want to write specific query to get data when importing data from MySQL database into Power BI Desktop? If so, the blogs below describe that how to  select first N rows for each category using MySQL query. You can refer to them.

http://www.sqlines.com/mysql/how-to/get_top_n_each_group
http://samalpramod.blogspot.com/2014/12/getting-top-n-rows-by-each-category-in.html


Regards,

Lydia

[Anonymous]

That helped thanks.

I now have a new problem, which is that some of the columns are titled with a dot in their name

e.g. customer.name

The query refuses to accept these, square brackets and backticks don't help either. Any ideas?

[Anonymous]

Got it!

Single quotes ' around a column name with a dot will allow the query to be accepted.

For reference, if you have a DB with an invalid character (in the From part), then you need to encase it in backticks

[v-yuezhe-msft]

@Anonymous，

Please open a new thread about the new issue.

Regards,
Lydia