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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors