Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 : 

2018_08_21_15_15_39_Untitled_Power_Query_Editor.png

Thank you, 

 

Lorenna 

 

1 ACCEPTED SOLUTION
MFelix
Super User
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"

clears rows.gif

 

Regards,

MFelix

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
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"

clears rows.gif

 

Regards,

MFelix

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi @MFelix

 

This works perfectly ! Thank you so much 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.