I've an Excel worksheet like this: How can I say in Power Query (visual, or M language) to discard all the rows starting from the first empty line?

You can use Table.FirstN like:Table.FirstN(myTable, each _ <> [ORDINE = "", ANNO = "", MESE = "", GIORNO = ""]) Docs: Table.FirstN - PowerQuery M | Microsoft Docs

discard all the rows starting from the first empty line

5 Replies

artemus
Microsoft Employee
4 years ago
You can use Table.FirstN like:
Table.FirstN(myTable, each _ <> [ORDINE = "", ANNO = "", MESE = "", GIORNO = ""])

Docs: Table.FirstN - PowerQuery M | Microsoft Docs

Vijay_A_Verma

Most Valuable Professional

4 years ago

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

Note - Replace value step will not be necessary for you as this has been done to generate nulls. If you are having blanks, then this will be needed.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyUdJRMjIwMgBShiDCWClWByRuDBE3AlKmQGwGFgYyoAjES0tLgQuYg0VMwCoRKkxhkjpKFkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ORDINE = _t, ANNO = _t, MESE = _t, GIRNO = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ORDINE", type text}, {"ANNO", Int64.Type}, {"MESE", Int64.Type}, {"GIRNO", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"ORDINE"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each List.NonNullCount(Record.ToList(_))),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1, Int64.Type),
    BuffList = #"Added Index"[Custom],
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom.1", each List.Count(List.Select(List.FirstN(BuffList,[Index]), each _=0))>0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = false)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Index", "Custom.1"})
in
    #"Removed Columns"

Vijay_A_Verma
Most Valuable Professional
4 years ago
artemus has given a good solution. In your context, you can use following where you will need to replace #"Replaced Value" with your previous step.
= Table.FirstN(#"Replaced Value",each List.NonNullCount(Record.ToList(_))>0)
- 6DiegoDiego9
  Helper I
  4 years ago
  Wow thanks artemus and Jijay!
  What's the meaning of the "each List.NonNullCount(Record.ToList(_))>0" part?
  - Vijay_A_Verma
    Most Valuable Professional
    4 years ago
    List.NonNullCount(Record.ToList(_))>0
    _ : Current Record
    Record.ToList(_) : Gives a list containing all values from a record
    List.NonNullCount(Record.ToList(_)) : Counts how many non null values are there in that record
    List.NonNullCount(Record.ToList(_))>0 - Means records which have all non nulls
    Table.FirstN - Will pick up those first records where above condition is true. The moment, it encounters where this condition is false, it will ignore all subsequent records.

Forum Discussion

discard all the rows starting from the first empty line