Forum Discussion

Jeffreyjar's avatar
Jeffreyjar
Helper II
3 years ago
Solved

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         

 

rsbin                     

ryan_mayu 

AlexisOlson 

  • 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

  • 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"