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

[memoalkatib1]

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...

[slorin]

Hi

Group by Value

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

Stéphane 

[memoalkatib1]

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.

[AlienSx]

@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? 

[memoalkatib1]

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

[AlienSx]

@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}

[memoalkatib1]

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.