Power Query Excel Data Transformation Question

[vbaNumpty]

I have a column that I would like to change the values in the cell where there is no error to be the value in another column, if the value is null then I want to replace it with the word "CONFIRMED"

What is the proper way of doing this in the editor?

example of what I am looking for:

Before:

Column1	Column2
PREBOOK	2021-01-01
Yes	Error
No	Error
null	2021-01-02

after:

Column1	Column2
PREBOOK	PREBOOK
Yes	Error
No	Error
null	CONFIRMED

 

[severynm]

You cannot transform a column with a custom formula - only add a new one. This is also a little more complex because you dont want to do anything if there are errors - only if there are not errors. There might be more elegant solutions, but this should work. Create a helper column to determine if Column2 has an error by performing a try on Column 2, and pulling out the HasError column that results from the error record:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Column2.HasError", each try [Column2]),
#"Expanded NewColumnName" = Table.ExpandRecordColumn(#"Added Custom", "Column2.HasError", {"HasError"}, {"Column2.HasError"})

, where Column2.HasError evaluates to True or False depending on Column2.

Then you can add your final column with the formula:

= Table.AddColumn(#"Expanded NewColumnName", "Column2.New", each if not [Column2.HasError] then if [Column1] = null then "CONFIRMED" else [Column1] else [Column2])

 

[vbaNumpty]

You cannot transform a column with a custom formula - only add a new one. This is also a little more complex because you dont want to do anything if there are errors - only if there are not errors. There might be more elegant solutions, but this should work. Create a helper column to determine if Column2 has an error by performing a try on Column 2, and pulling out the HasError column that results from the error record:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Column2.HasError", each try [Column2]),
#"Expanded NewColumnName" = Table.ExpandRecordColumn(#"Added Custom", "Column2.HasError", {"HasError"}, {"Column2.HasError"})

, where Column2.HasError evaluates to True or False depending on Column2.

Then you can add your final column with the formula:

= Table.AddColumn(#"Expanded NewColumnName", "Column2.New", each if not [Column2.HasError] then if [Column1] = null then "CONFIRMED" else [Column1] else [Column2])

View attachment 41782

I am having trouble implementing your solution, I am getting an error saying Column2.HasError cannot be found.

 

[vbaNumpty]

You cannot transform a column with a custom formula - only add a new one. This is also a little more complex because you dont want to do anything if there are errors - only if there are not errors. There might be more elegant solutions, but this should work. Create a helper column to determine if Column2 has an error by performing a try on Column 2, and pulling out the HasError column that results from the error record:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Column2.HasError", each try [Column2]),
#"Expanded NewColumnName" = Table.ExpandRecordColumn(#"Added Custom", "Column2.HasError", {"HasError"}, {"Column2.HasError"})

, where Column2.HasError evaluates to True or False depending on Column2.

Then you can add your final column with the formula:

= Table.AddColumn(#"Expanded NewColumnName", "Column2.New", each if not [Column2.HasError] then if [Column1] = null then "CONFIRMED" else [Column1] else [Column2])

View attachment 41782

I have gotten to the Expanded NewColumnName step but aftter the added custom Table.AddColumn(#"Changed Type", "Column2.HasError", each try [Column2]), my column2 is gone.

 

[severynm]

What is your entire query from the Advanced Editor, and how does it differ than what I pasted below? It is entirely I possible I have made a typo earlier when adding the code here.

Note that the first three steps (Source, Replaced Value, Changed Type) are steps I took to replicate your data. Those first three steps will likely be different for you because you are getting the data directly, and not entering it manually by hand.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCghydfL391bSUTIyMDLUNQAhpVidaKXI1GKgYCKY7ZcPZCaBmcgKjZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column1"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Column1", type text}, {"Column2", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Column2.HasError", each try [Column2]),
    #"Expanded NewColumnName" = Table.ExpandRecordColumn(#"Added Custom", "Column2.HasError", {"HasError"},{"Column2.HasError"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded NewColumnName", "Column2.New", each if not [Column2.HasError] then if [Column1] = null then "COMPLETED" else [Column1] else [Column2])
in
    #"Added Custom1"

 

[vbaNumpty]

What is your entire query from the Advanced Editor, and how does it differ than what I pasted below? It is entirely I possible I have made a typo earlier when adding the code here.

Note that the first three steps (Source, Replaced Value, Changed Type) are steps I took to replicate your data. Those first three steps will likely be different for you because you are getting the data directly, and not entering it manually by hand.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCghydfL391bSUTIyMDLUNQAhpVidaKXI1GKgYCKY7ZcPZCaBmcgKjZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column1"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Column1", type text}, {"Column2", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Column2.HasError", each try [Column2]),
    #"Expanded NewColumnName" = Table.ExpandRecordColumn(#"Added Custom", "Column2.HasError", {"HasError"},{"Column2.HasError"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded NewColumnName", "Column2.New", each if not [Column2.HasError] then if [Column1] = null then "COMPLETED" else [Column1] else [Column2])
in
    #"Added Custom1"

Found the mistake on my end, I had not put the correct previous step in one of the Let lines.

 

[vbaNumpty]

You cannot transform a column with a custom formula - only add a new one. This is also a little more complex because you dont want to do anything if there are errors - only if there are not errors. There might be more elegant solutions, but this should work. Create a helper column to determine if Column2 has an error by performing a try on Column 2, and pulling out the HasError column that results from the error record:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Column2.HasError", each try [Column2]),
#"Expanded NewColumnName" = Table.ExpandRecordColumn(#"Added Custom", "Column2.HasError", {"HasError"}, {"Column2.HasError"})

, where Column2.HasError evaluates to True or False depending on Column2.

Then you can add your final column with the formula:

= Table.AddColumn(#"Expanded NewColumnName", "Column2.New", each if not [Column2.HasError] then if [Column1] = null then "CONFIRMED" else [Column1] else [Column2])

View attachment 41782

Thank you for your assistance everything works a peach! Greatly appreciate it.