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.