Forum Discussion
Power Query - How to ignore column with invalid first value (#REF!) while promoting headers
You can use the "Replace Errors" transform to replace the #REF! with something meaningful
Consider the next image as your data with the name Source, use the next formula to remove the columns with the error value in the first row.
= Table.SelectColumns(Source,List.Accumulate(Table.ColumnNames(Source),{},(a,b)=> if (try Table.Column(Source,b){0})[HasError] then a else a & {b}))
data=>
result=>
Hi,
Thanks for the solution Omid_Motamedise and lbendlin offered, and i want to offer some more information for user to refer to.
hello Shantanu3006 , you can create a blank query and input the following code to advanced editor in power query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY27DsIwDEV/BcLaoeTZjJS2wICE2FDUIUOBLkSKsvD3XEMn5OHY1j12EoK4TTGLSpzTqzzRu/hG3ccyPVKm8ZLTfS4YNtd+WIuxCkLWUiPYLuyA1BJ19a8pbIGSWrELij6jbrTiPPUOWGc5bUC/nLMLFA6gcUpz3oEDeWvY+wYcga8568Hp+7p3Pz9+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column7 = _t]), #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Column7", Int64.Type}}), #"Transposed Table" = Table.Transpose(#"Changed Type1"), #"Removed Errors" = Table.RemoveRowsWithErrors(#"Transposed Table", {"Column1"}), #"Transposed Table1" = Table.Transpose(#"Removed Errors"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]) in #"Promoted Headers"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
I have data in an excel sheet like below. I have few columns (from Year till Profit) to be imported in the power query. There is also a column with an invalid value (#REF!). When I try to promote the headers I get an error saying invalid data format. I understand its becasue of this invalid #REF! value. My question is how do I ignore this column while promoting the headers of this table, so that I can get only columns from Year to Profit.
I have multiple sheets to be combined after promoting the headers so I can not expand the columns simply from UI and select what is required.
Any help in this regard is very helpful and appreciated.
| Year | Month | Day | Category | Profit | #REF! | |
| 2024 | 1 | 1 | A | 242 | ||
| 2024 | 1 | 2 | B | 32432 | ||
| 2024 | 1 | 3 | C | 3543 | ||
| 2024 | 1 | 4 | D | 676 | ||
| 2024 | 1 | 5 | E | 35436 | ||
| 2024 | 1 | 6 | F | 8734 | ||
| 2024 | 1 | 7 | G | 8656 | ||
| 2024 | 1 | 8 | H | 90 | ||
| 2024 | 1 | 9 | I | 8797 |
Thanks
Shantanu
Consider the next image as your data with the name Source, use the next formula to remove the columns with the error value in the first row.
= Table.SelectColumns(Source,List.Accumulate(Table.ColumnNames(Source),{},(a,b)=> if (try Table.Column(Source,b){0})[HasError] then a else a & {b}))
data=>
result=>
Hi,
Thanks for the solution Omid_Motamedise and lbendlin offered, and i want to offer some more information for user to refer to.
hello Shantanu3006 , you can create a blank query and input the following code to advanced editor in power query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY27DsIwDEV/BcLaoeTZjJS2wICE2FDUIUOBLkSKsvD3XEMn5OHY1j12EoK4TTGLSpzTqzzRu/hG3ccyPVKm8ZLTfS4YNtd+WIuxCkLWUiPYLuyA1BJ19a8pbIGSWrELij6jbrTiPPUOWGc5bUC/nLMLFA6gcUpz3oEDeWvY+wYcga8568Hp+7p3Pz9+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column7 = _t]), #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Column7", Int64.Type}}), #"Transposed Table" = Table.Transpose(#"Changed Type1"), #"Removed Errors" = Table.RemoveRowsWithErrors(#"Transposed Table", {"Column1"}), #"Transposed Table1" = Table.Transpose(#"Removed Errors"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]) in #"Promoted Headers"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.