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
Licantrop0
Employee
Employee

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:

Licantrop0_1-1598451641609.png

 

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!

1 ACCEPTED SOLUTION

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"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

5 REPLIES 5
TheBoojum
Frequent Visitor

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:

https://www.thebiccountant.com/2016/03/08/select-rows-that-have-no-empty-fields-using-expression-eva... 

 

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.

amitchandak
Super User
Super User

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Wow, I too hoped that the solution would be simpler, but the helper syntax provided is super helpful and explained it all in a very straightforward manner, also allowing me to learn new skills.  Thank you! @ImkeF 

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.