How to concatenate two columns together in Edit Queries?

[Anmolgan]

I have a below dataset:

SAP State Names      My State Names

A1                                  A1

A2                                  A2

A3                                  B3

A4                                  B4

A5       

A6

i want to merge both the fields in such a way that values that do not match SAP State Names Column from My State Names should get replaced with My State Names entries and where ever there is a blank then reeplace SAP State names with the blanks for My Statee Names, output should look like below:

Custom Column

A1

A2

B3

B4

A5

A6

is there any way to achive this using custom columns or edit queries??

[ChrisMendoza]

@Anmolgan -

= Table.AddColumn(#"Changed Type", "Custom", each if [My state Names] <> "" then [My state Names] else [SAP State Names])

[Anmolgan]

@ChrisMendoza This gives me a merged table and not the respective output

[ChrisMendoza · Accepted solution]

@Anmolgan - remove the columns that created the calculated column?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0gERsTpAthGIbQRhGwPZTsYQtgmIbQJhmwLZEJYZmBULAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"SAP State Names" = _t, #"My state Names" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SAP State Names", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [My state Names] <> "" then [My state Names] else [SAP State Names]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"})
in
    #"Removed Other Columns"

[kentyler]

Your logic could be boiled down to just showing the SAP State Names.

If you have cases where you have a state name that is not in SAP State Names then you might need a calculated column with some IF() statements.

[Anmolgan]

Can you elaborate more @kentyler 

[kentyler]

you said

values that do not match SAP State Names Column from My State Names should get replaced with My State Names entries and where ever there is a blank then reeplace SAP State names with the blanks for My Statee Names, 

this means that anytime there is a value in the SAP State Names column it falls under one of your 2 conditions....

so the values in your custom column will always be the same values that you have in your state names column