Forum Discussion
Two variations of first row text causing problems when used in column names
Hello,
I have data that comes into Power Query in two variations. Column 25 first row can be named 1) Temperature Deviation (°C) as in the sample data or 2) Temperature Deviation (°F).
When I promote the headers and use hard coded “Temperature Deviation (°C)” in the following steps transforming the data, I get errors in the cases when the imported data has "Temperature Deviation °F". Error: “The column 'Temperature Deviation (°C)' of the table wasn't found.”
I can fix this manually, but I am trying to automate things so I would not have to change things manually every time.
The goal would be to have a column with a name “Temperature Deviation”. I would like to use that as a hard coded column name in the following steps. So whether the text in the first row of imported data would contain °C or °F, those would be removed. These changes would only apply to the first row, otherwise the data would be used as it is in the column. I don't need the information of °C or °F.
I have tried the following, but it doesnt seem to be the right solution:
= Table.ReplaceValue(Source,
each [Column25],
each if [Column25] = "Temperature Deviation (°C)" then [Temperature Deviation]
else if [Column25] = "Temperature Deviation (°F)" then [Temperature Deviation]
else [Column25],
Replacer.ReplaceText,{"Column25"}
Can I solve this problem with conditional replace or conditional column? I have been trying to search this forum, but it seems like I cannot figure out right keywords
|
After your Source statement, this should be your second statement. This will change the column name to "Temperarure Deviation". Any processing like Change Type or anything should only after this statement. Insert this step in your query
Table.TransformColumnNames(Source, (x)=>if Text.Contains(x,"Temperature Deviation") then "Temperature Deviation" else x)If you want to see this working - See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVtJRcgNiE1OlWJ1opbR0IARyfYFY19hEKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Gender = _t, #"Temperature Deviation (°C)#(lf)" = _t]), Custom1 = Table.TransformColumnNames(Source, (x)=>if Text.Contains(x,"Temperature Deviation") then "Temperature Deviation" else x), #"Changed Type" = Table.TransformColumnTypes(Custom1,{{"Name", type text}, {"Gender", type text}, {"Temperature Deviation", Int64.Type}}) in #"Changed Type"
2 Replies
After your Source statement, this should be your second statement. This will change the column name to "Temperarure Deviation". Any processing like Change Type or anything should only after this statement. Insert this step in your query
Table.TransformColumnNames(Source, (x)=>if Text.Contains(x,"Temperature Deviation") then "Temperature Deviation" else x)If you want to see this working - See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVtJRcgNiE1OlWJ1opbR0IARyfYFY19hEKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Gender = _t, #"Temperature Deviation (°C)#(lf)" = _t]), Custom1 = Table.TransformColumnNames(Source, (x)=>if Text.Contains(x,"Temperature Deviation") then "Temperature Deviation" else x), #"Changed Type" = Table.TransformColumnTypes(Custom1,{{"Name", type text}, {"Gender", type text}, {"Temperature Deviation", Int64.Type}}) in #"Changed Type"Thanks for help, worked just as expected !
