discard all the rows starting from the first empty line

[6DiegoDiego9]

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?

[artemus · Accepted solution]

You can use Table.FirstN like:

Table.FirstN(myTable, each _ <> [ORDINE = "", ANNO = "", MESE = "", GIORNO = ""])

Docs: Table.FirstN - PowerQuery M | Microsoft Docs

[Vijay_A_Verma · Accepted solution]

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

Wow thanks artemus and Jijay!
What's the meaning of  the "each List.NonNullCount(Record.ToList(_))>0" part?

[Vijay_A_Verma]

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.

[artemus · Accepted solution]

You can use Table.FirstN like:

Table.FirstN(myTable, each _ <> [ORDINE = "", ANNO = "", MESE = "", GIORNO = ""])

Docs: Table.FirstN - PowerQuery M | Microsoft Docs

[Vijay_A_Verma]

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"