Forum Discussion
link two columns with powerquery with condition
- 3 years ago
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"
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"- Jeffreyjar3 years agoHelper II
thanks it works