March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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!
Solved! Go to 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
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.
@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...
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
92 | |
86 | |
77 | |
49 |
User | Count |
---|---|
166 | |
149 | |
99 | |
73 | |
57 |