Forum Discussion
Transform Column issue
Hello
I am trying to get better at PowerQuery... so I could this with a number of steps but I am trying to reach this in a better way.
I have a column that has blank values. I need to replace its (blank) values with a concatenation of the text of 3 other columns. I have attempted to do so but miserabily failed....
= Table.TransformColumns(#"Filtered Rows",
{"PROD_ORDER_NUMBER",
each [PRODUCT_NAME] & " " & [FACILITY] & " " & Text.From([PRODUCTION_START]),
type text
} )
This errors out to
Expression.Error: We cannot apply field access to the type Text.
Details:
Value=
Key=PRODUCT_NAME
So I am not sure how to reference the columns or build this formula correctly...
Thank you so much for your help!
Kind regards
Valeria
Hi ValeriaBreve ,
How about the following:
Before:
After:
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMjQyBpJOOaWpQEopVidaycXVDcg0MTUDku5Fqal5QLokMTm7Eizr7uEJ5JtbWALJyNScnPxykHRqcgZY1svbB2SmgaEhkApKTYGZ6evnD7bLEGRZQGlRQQ7IupL8XKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, TestColumn = _t]), #"PreviousStep" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"TestColumn"}), #"Replace Values" = Table.ReplaceValue(#"PreviousStep",each [TestColumn],each if [TestColumn] is null then [Column1] & [Column2] & [Column3] else [TestColumn],Replacer.ReplaceValue,{"TestColumn"}) in #"Replace Values"It should be the "replace values" step that you are interested in.
I usually use the steps described in here to replace values in a column.
https://www.tackytech.blog/how-to-swiftly-take-over-power-query/#replace-values-in-column
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
4 Replies
Hi ValeriaBreve ,
How about the following:
Before:
After:
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMjQyBpJOOaWpQEopVidaycXVDcg0MTUDku5Fqal5QLokMTm7Eizr7uEJ5JtbWALJyNScnPxykHRqcgZY1svbB2SmgaEhkApKTYGZ6evnD7bLEGRZQGlRQQ7IupL8XKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, TestColumn = _t]), #"PreviousStep" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"TestColumn"}), #"Replace Values" = Table.ReplaceValue(#"PreviousStep",each [TestColumn],each if [TestColumn] is null then [Column1] & [Column2] & [Column3] else [TestColumn],Replacer.ReplaceValue,{"TestColumn"}) in #"Replace Values"It should be the "replace values" step that you are interested in.
I usually use the steps described in here to replace values in a column.
https://www.tackytech.blog/how-to-swiftly-take-over-power-query/#replace-values-in-column
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Thank you! Super clear 🙂 Not only I have the answer to the question but I will know how to build this type of fucntions in the future. The link you gave is saved on my favourites :-). Thanks again!
Hi ValeriaBreve ,
This is awesome! I tried to write that blog article in a sort of "cook book recipe" style. So, there might be other useful things in there, too 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Yes indeed! I have found it super useful not only for the ReplaceValue part but as a whole. Thanks for taking the time to write and share!
