Delete first row, last row and set header for multiple excel sheets

Hi Anonymous,

In the query editor you should select one of the columns were the first and last row is null then just filter the null out of the column.

The just promote the first row as header.

I have made a similar mockup file from your see the M query code and PBIX file attach.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8swry89MTlUwVNJBsHUUHENCgAJQFKuDqs6/KKUoNa80Nze1CKTLRRfO9s7MSUktLijKTwdyfBNzIEx0/YZGQMLYBEgkgjF++ZRBp99TITGnBHvwGBEZjEYUBiNIv6ExyJmmQCIJwxuY8ikDrJ908+Fs9HCMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column3] <> "")),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Invoice 1", type text}, {"Ordrenummer", type text}, {"ID-nummer", type text}, {"Kildesprog", type text}, {"Malsprog", type text}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([#"ID-nummer"] <> "ID-nummer"))
in
    #"Filtered Rows1"

Regards,

MFelix