Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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"
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"
thanks it works
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.