Forum Discussion
link two columns with powerquery with condition
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
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"
2 Replies
- AlexisOlsonSuper User
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"- JeffreyjarHelper II
thanks it works