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

Question

Hi There,&nbsp;&nbsp;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 :&nbsp;Value&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; List&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LabelS_SPONSOR_ID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DK&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Do not know&nbsp;S_SPONSOR_ID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;N/A&nbsp; S_SPONSOR_ID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Refusal&nbsp;S_ORGANIZATION&nbsp; &nbsp; &nbsp; &nbsp; NA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;N/A&nbsp;S_ORGANIZATION&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RefusalS_ORGTYPE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Private&nbsp;S_ORGTYPE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Public&nbsp;S_ORGTYPE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Not-for profit&nbsp; &nbsp;S_ORGTYPE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;OtherExample article i found :&nbsp;https://www.thepoweruser.com/2019/12/19/split-segment-partition-section-your-table-in-power-bi-power-query/

aliensx · Answer

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

slorin · Answer

Hi&nbsp;Group by Value&nbsp;= Table.Group(Source, {"Value"}, {{"Data", each _}})Stéphane&nbsp;

memoalkatib1 · Answer

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&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DataS_SPONSOR_ID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TableS_ORGANIZATION&nbsp; &nbsp; &nbsp; &nbsp;TableS_ORGTYPE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;TableIf I just save this as is I get the message :&nbsp;Expression.Error: We cannot convert a value of type Table to type Text.

memoalkatib1 · Answer

The grouping will give me 2 columns in the form of a list. no ? identical to this breakdown :&nbsp;Value&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DataS_SPONSOR_ID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TableS_ORGANIZATION&nbsp; &nbsp; &nbsp; &nbsp;TableS_ORGTYPE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Tablemy 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?&nbsp; 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 · Answer

memoalkatib1&nbsp;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?&nbsp;

Forum Discussion

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

6 Replies

Related content

regarding storing multiple tables in lake house

Use a table multiple time

Table.Split by a delimeter

Load multiple tables using query

Filtering a table with multiple conditions from multiple switches pulled from another table