Forum Discussion
Split table in multiple tables by blank rows
I have a single CSV file that is actually separate tables.
The tables are separate by a blank row, after which there is a new header:
The number of rows in each sub-table may change, so I cannot use "keep first N rows".
I need a function to split this table into multiple tables whenever there is a blank row.
Or at least an M function to tell me what is the first blank row number so I can use it as parameter of the Table.Skip function.
Thanks for any help!
Hi Licantrop0 ,
you can create a staging table that holds the partitions to be referenced by further queries.
Please paste the following code into the advanced editor and follow the steps:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8khNTEktMlTSgbKMgCylWJ1oJZCQMYwDEjWBcUA0lIlNN4RljGKIKYopZhA5AyDTCEQYG6Cb65iXX5KRWqSQATYLScIQiW0EY8cCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Column1] = "" then [Index] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}), #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Column1] <> "")), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Custom"}, {{"All", each _, type table [Column1=nullable text, Column2=nullable text, Column3=nullable text, Custom=nullable number]}}) in #"Grouped Rows"
5 Replies
ImkeF , can you help on this
Hi Licantrop0 ,
you can create a staging table that holds the partitions to be referenced by further queries.
Please paste the following code into the advanced editor and follow the steps:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8khNTEktMlTSgbKMgCylWJ1oJZCQMYwDEjWBcUA0lIlNN4RljGKIKYopZhA5AyDTCEQYG6Cb65iXX5KRWqSQATYLScIQiW0EY8cCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Column1] = "" then [Index] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}), #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Column1] <> "")), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Custom"}, {{"All", each _, type table [Column1=nullable text, Column2=nullable text, Column3=nullable text, Custom=nullable number]}}) in #"Grouped Rows"Thanks so much ImkeF !
That's a very good workaround.
I hoped there was a more streamlined solution like:
Table.Split([TableName], <condition>)but I guess I'll have to open a new uservoice item for it...
I know this is a slightly old post, but I had a similar problem and found this article which gives a very good way of flagging empty rows:
There is a really neat approach documented in the comments:
= Table.SelectRows(Source, each List.AllTrue(List.Transform(Record.FieldValues(_), (fieldVal) => fieldVal <> null)))
- just add a new column with the code after 'each' and change the '<> null' to '= null'.
Having identified the null rows, you can play around with indices to split the data as required.
