How to split rows by 5000 records?

[Anonymous]

Dear All,

I have a 100k+ rows table.

I have a task to split this table by 5000 records each. It doesn't matter in what order to arrange the records. I just need to get 20+ tables that will contain 5000 rows each.

I believe there should be some way to do it smart to minimize manual work.

Please, help me!

[dedelman_clng · Accepted solution]

This might be more manual than you want, but you could do this:

1) Set an INDEX number for the original table (it's in Query Editor under Add Coluimn)

2) Under Modeling, select New Table and create 20 calculated tables like so:

Table1 = CALCULATETABLE(
               OrigTable,
               OrigTab[Index] >=1 && OrigTab[Index] <= 5000
)

Table2 = CALCULATETABLE(
               OrigTable,
               OrigTab[Index] >=5001 && OrigTab[Index] <= 10000
)

etc

Hope this helps

David

[dedelman_clng · Accepted solution]

This might be more manual than you want, but you could do this:

1) Set an INDEX number for the original table (it's in Query Editor under Add Coluimn)

2) Under Modeling, select New Table and create 20 calculated tables like so:

Table1 = CALCULATETABLE(
               OrigTable,
               OrigTab[Index] >=1 && OrigTab[Index] <= 5000
)

Table2 = CALCULATETABLE(
               OrigTable,
               OrigTab[Index] >=5001 && OrigTab[Index] <= 10000
)

etc

Hope this helps

David

[Anonymous]

Dedelman_clng,

Thank you for the fast reply and help!

I didn't see the 'Modeling'. Where is it?

You gave me an idea how to do it. I'll create a filter using the Index column and will sort the DB this way. In fact,  I need these tables in CSV so there is no need to create separate queries.

Thank you so much for the inspiration!

[Anonymous]

I found the modeling. I thought it was in Queries first.

Thank you!

[Anonymous · Accepted solution]

Update:

I found the easier way to do it. Maybe it will help someone else.

1. Create an Index column
2. Add the Index as a filter on the report view
3. Click 'New Group' in the fields menu
4. Set the required bin size. That is!

That is why I love PowerBi 🙂

[dedelman_clng]

I didn't know that about the bin size - thanks for the tip!