cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

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

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

Hi @ValeriaBreve ,

 

How about the following:

 

Before:

 

 

After:

 

And here the M code I used:

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 null,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!

Syndicate_Admin
Administrator
Administrator

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!

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors
Users online (2,412)