Transform Column issue

[ValeriaBreve]

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

[tackytechtom]

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/

[ValeriaBreve]

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!

[tackytechtom · Accepted solution]

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/

[ValeriaBreve]

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!