Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to split rows by 5000 records?

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!

2 ACCEPTED SOLUTIONS
dedelman_clng
Community Champion
Community Champion

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

View solution in original post

Anonymous
Not applicable

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 🙂

View solution in original post

5 REPLIES 5
dedelman_clng
Community Champion
Community Champion

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
Not applicable

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
Not applicable

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

 

Thank you!

Anonymous
Not applicable

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 🙂

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.