Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |