Copying value to another row based on another column

[joooffice]

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:

ID	Name	Outstanding?
123	Joe
123	Sam	Fee Due
208	Helen
156	Bob	Fee Due
123	Fred
156	Mike
189	Carol	Fee Due
156	Anne
208	Lucy
208	Tony

And i want the result to be:

ID	Name	Outstanding?
123	Joe	Fee Due
123	Sam	Fee Due
208	Helen
156	Bob	Fee Due
123	Fred	Fee Due
156	Mike	Fee Due
189	Carol	Fee Due
156	Anne	Fee Due
208	Lucy
208	Tony

[Anonymous]

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"

[ziying35 · Accepted solution]

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

[Mariusz]

Hi @joooffice 

Sure on the attached, you will find a solution, just follow the query steps.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[joooffice]

Thanks Marius, I cant open the attchement though

Joanne