Forum Discussion
Delete first row, last row and set header for multiple excel sheets
I'm new with PowerBI and I'd like to automate some reports.
I have numerous excel files into a folder. Each excel files have multiple sheets.
Ideally :
- All excel sheets should have the first row and the last row deleted.
- Then first row set as header
Could someone please give me some guidance how to create a function which would perform the above on all excel sheets?
When data are appened i get unwanted rows as shown in the screenshot :
Thank you,
Lorenna
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
2 Replies
- MFelix
Super User
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