Forum Discussion

joooffice's avatar
joooffice
Helper I
6 years ago
Solved

Copying value to another row based on another column

Hi

 

I'm trying to find a way to copy the contents of the Outstanding column to any other row that has the same ID number. The ID's are not necessarily sequential or (and i dont want to change the order) so i can't use row above/below

My data looks like:

IDNameOutstanding?
123Joe 
123SamFee Due
208Helen 
156BobFee Due
123Fred 
156Mike 
189CarolFee Due
156Anne 
208Lucy 
208Tony 

 

And i want the result to be:

IDNameOutstanding?
123JoeFee Due
123SamFee Due
208Helen 
156BobFee Due
123FredFee Due
156MikeFee Due
189CarolFee Due
156AnneFee Due
208Lucy 
208Tony 
Need Help
  • ziying35's avatar
    ziying35
    6 years ago

    Hi, joooffice 

    let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65W8nRRsjI0MtZR8kvMTVWyUvLKT1XSUfIvLSkuScxLycxLt1eyyivNyanVwVQbnJiLoVbJLTVVwaU0VQmmwcjAAq7BIzUnNQ+/8aZmcNVO+UmEjUd2j1tRagrRpvtmZhPwqYUlXLFzYlF+DhGOQTLfMS8Pv/nIAeNTmlxJtOKQ/DwcimMB",BinaryEncoding.Base64),Compression.Deflate))),
    fx = (tbl, id)=> Table.SelectRows(tbl, (r)=> (r[ID] = id) and (r[#"Outstanding?"] <> null)){0}?[#"Outstanding?"],
    result = Table.ReplaceValue(Source, each fx(Source, [ID]), each [#"Outstanding?"]=null, (x,y,z)=>if z then y else x, {"Outstanding?"})
in
    result

    If my code solves your problem, mark it as a solution

    ziying

4 Replies

  • ziying35's avatar
    ziying35
    Impactful Individual
    6 years ago

    Hi, joooffice 

    let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65W8nRRsjI0MtZR8kvMTVWyUvLKT1XSUfIvLSkuScxLycxLt1eyyivNyanVwVQbnJiLoVbJLTVVwaU0VQmmwcjAAq7BIzUnNQ+/8aZmcNVO+UmEjUd2j1tRagrRpvtmZhPwqYUlXLFzYlF+DhGOQTLfMS8Pv/nIAeNTmlxJtOKQ/DwcimMB",BinaryEncoding.Base64),Compression.Deflate))),
    fx = (tbl, id)=> Table.SelectRows(tbl, (r)=> (r[ID] = id) and (r[#"Outstanding?"] <> null)){0}?[#"Outstanding?"],
    result = Table.ReplaceValue(Source, each fx(Source, [ID]), each [#"Outstanding?"]=null, (x,y,z)=>if z then y else x, {"Outstanding?"})
in
    result

    If my code solves your problem, mark it as a solution

    ziying

    • joooffice's avatar
      joooffice
      Helper I
      6 years ago

      Thanks Marius, I cant open the attchement though

       

      Joanne 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi joooffice 

     

    You can try below solution:

    let
    //Reference To my source Table
    Source = Table,

    // Dictionary for Outstanding Cases
    #"Removed Columns" = Table.RemoveColumns(Source,{"Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([#"Outstanding?"] = "Fee Due")),
    OutstandingDict = Table.Distinct(#"Filtered Rows"),

    //Remove Outstanding? Column from Source
    #"Removed Column1" = Table.RemoveColumns(Source,{"Outstanding?"}),
    //Combine With Dictionary
    #"Merged Queries" = Table.NestedJoin(#"Removed Column1", {"ID"}, OutstandingDict, {"ID"}, "OutstandingDict", JoinKind.LeftOuter),
    //Get New Oustanding Column
    #"Expanded OutstandingDict" = Table.ExpandTableColumn(#"Merged Queries", "OutstandingDict", {"Outstanding?"}, {"Outstanding?"})

in
    #"Expanded OutstandingDict"