link two columns with powerquery with condition

[Jeffreyjar]

Hello, i have an issue, i want to link column EEAGENT with column EAFCR but with one specification like 

- Fill empty  rows from EAAGENT to EAFCR (with power query) 

EEAGENT	EAFCR
A10399028	A10399026
A10399029	A10399027
A10199002
A10199003

Kind Regards         

@rsbin                     

@ryan_mayu 

@AlexisOlson 

[AlexisOlson · Accepted solution]

Select one of the empty cells and click Transform > Replace Values. This should open up a box where you can specify what to replace. Leave the first box empty and put something in the second box.

This should generate a step like

= Table.ReplaceValue(Source,"","placeholder",Replacer.ReplaceValue,{"EAFCR"})

Now all we need to do is tweak the generated code to replace the placeholder with "each [EEAGENT]":

= Table.ReplaceValue(Source,"",each [EEAGENT],Replacer.ReplaceValue,{"EAFCR"})

Result:

Full sample code you can paste into the Advanced Editor of a new blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQ0MLa0NDCyUNKBs82UYnUQMpZIMuYwGUMgz8AIKIMiYAwWiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EEAGENT = _t, EAFCR = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",each [EEAGENT],Replacer.ReplaceValue,{"EAFCR"})
in
    #"Replaced Value"

[Jeffreyjar]

thanks it works