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

[Shantanu3006]

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

[lbendlin · Accepted solution]

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

[Omid_Motamedise · Accepted solution]

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

[Shantanu3006]

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

[Omid_Motamedise · Accepted solution]

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

[v-xinruzhu-msft · Accepted solution]

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.

[lbendlin · Accepted solution]

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