Forum Discussion

Shantanu3006's avatar
Shantanu3006
New Member
2 years ago
Solved

Power Query - How to ignore column with invalid first value (#REF!) while promoting headers

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.

 

YearMonthDayCategoryProfit #REF!
202411A242  
202412B32432  
202413C3543  
202414D676  
202415E35436  
202416F8734  
202417G8656  
202418H90  
202419I8797  

 

Thanks

Shantanu

Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    You can use the "Replace Errors"  transform to replace the #REF!  with something meaningful

     

     

     

     

     

     

     

     

  • Omid_Motamedise's avatar
    Omid_Motamedise
    2 years ago

    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=>

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    You can use the "Replace Errors"  transform to replace the #REF!  with something meaningful

     

     

     

     

     

     

     

     

  • Shantanu3006's avatar
    Shantanu3006
    New Member
    2 years ago

    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.

     

    YearMonthDayCategoryProfit #REF!
    202411A242  
    202412B32432  
    202413C3543  
    202414D676  
    202415E35436  
    202416F8734  
    202417G8656  
    202418H90  
    202419I8797  

     

    Thanks

    Shantanu

    • Omid_Motamedise's avatar
      Omid_Motamedise
      Icon for Super User rankSuper User
      2 years ago

      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=>

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.