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
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
Solved! Go to Solution.
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.
You can use the "Replace Errors" transform to replace the #REF! with something meaningful