Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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êsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |