Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Shantanu3006
New Member

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

3 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

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

 

lbendlin_1-1724692841462.png

 

lbendlin_2-1724692856956.png

 

lbendlin_3-1724692907024.png

 

lbendlin_4-1724692926929.png

 

 

 

 

View solution in original post

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

Omid_Motamedise_0-1724714656784.png

 

result=>

Omid_Motamedise_1-1724714676044.png

 

View solution in original post

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

vxinruzhumsft_0-1724744661211.png

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.

 

 

View solution in original post

4 REPLIES 4
Shantanu3006
New Member

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

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

Omid_Motamedise_0-1724714656784.png

 

result=>

Omid_Motamedise_1-1724714676044.png

 

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

vxinruzhumsft_0-1724744661211.png

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
Super User
Super User

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

 

lbendlin_1-1724692841462.png

 

lbendlin_2-1724692856956.png

 

lbendlin_3-1724692907024.png

 

lbendlin_4-1724692926929.png

 

 

 

 

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors