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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
memoalkatib1
Frequent Visitor

Table.Split( ) need to split one flat table into multiple tables

Hi There, 

 

I'm importing an excel sheet that has a list of tables in the format of rows and columns . I found an article that almost solves my impediment using table.Split () . but the function only lets you breakdown the table by a static number of rows, example Table.Split (Source, 2) . how do I use the table.Split while each set of rows is different for each table that I have . I have hundered of rows and tables, so I don't want to manually refrence the main table then use manual step in and drop columns or rows in order to create the desired outcome. here is my example : 

Value                            List                  Label
S_SPONSOR_ID            DK                 Do not know
 S_SPONSOR_ID            NA                 N/A
  S_SPONSOR_ID          RE                     Refusal
 
S_ORGANIZATION        NA                       N/A
 S_ORGANIZATION          RE                          Refusal

S_ORGTYPE                    1                         Private
 S_ORGTYPE                    2                             Public
 S_ORGTYPE                3                          Not-for profit
   S_ORGTYPE                4                           Other

Example article i found : https://www.thepoweruser.com/2019/12/19/split-segment-partition-section-your-table-in-power-bi-power...
6 REPLIES 6
slorin
Super User
Super User

Hi

 

Group by Value

 

= Table.Group(Source, {"Value"}, {{"Data", each _}})

Stéphane 

I did that in a previous approach , but never got to how to expand the tables after this step. the grouping leaves me with this "Table" or view:

Value                           Data
S_SPONSOR_ID           Table
S_ORGANIZATION       Table
S_ORGTYPE                  Table

If I just save this as is I get the message : Expression.Error: We cannot convert a value of type Table to type Text.

@memoalkatib1 use slorin_table[Data] to get a list of tables. Table.Split gives the same structure - a list of tables and you're happy with that. What did you want to get in the end? 

The grouping will give me 2 columns in the form of a list. no ? identical to this breakdown : 

Value                           Data
S_SPONSOR_ID           Table
S_ORGANIZATION       Table
S_ORGTYPE                  Table

my question. what do I do after this step ? because I have yet to create individual tables for each row in the above example. so what would be the next stage?  table. split (source, 1) ? the Data column contains individual tables and I need to step in and transform those into stand alone tables and not have it as a list as shown above

@memoalkatib1 the only way to get 3 separate tables is to create 3 queries with tbl[Data]{0}, tbl[Data]{1} and tbl[Data]{2}

yeah, I was hoping that I can create a custom invoke function with and create a paramter list from 0 to approximately 500 (yes, I have that many dimension tables). and create a script and run it through the custom invoke function somehow passing through the script you mentioned above. 

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

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