Copy data from other rows based on same value in different columns and rows

Hi, gstover ;

According to your logic, I modified it a little:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMgRit6L8vBIgHQHEUBSrA1RgYgRkghQ5JSZnA6lINHljkLwRbnkgwwDNeEcgdgFiV7AKY0z9TkDsDNIEVmAAMc4xJwdJPwSDpI2gNmDXHwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Supervisor ID" = _t, Office = _t, #"Business Unit" = _t, Division = _t, #"Dept." = _t, Section = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Supervisor ID", Int64.Type}, {"Office", type text}, {"Business Unit", type text}, {"Division", type text}, {"Dept.", type text}, {"Section", type text}}),
    Custom1 = Table.NestedJoin(#"Changed Type", {"Supervisor ID"}, #"Changed Type", {"ID"}, "Custom1", JoinKind.LeftOuter),
    #"Expanded Custom1" = Table.ExpandTableColumn(Custom1, "Custom1", {"Division", "Dept.", "Section"}, {"Division.1", "Dept..1", "Section.1"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Custom1", "Division.Final", each if [Division] = "" then [Division.1] else [Division]),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Dept.final", each if [#"Dept."] = "" then [#"Dept..1"] else [#"Dept."]),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Section.final", each if [Section] = "" then [Section.1] else [Section]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column2",{"Division", "Dept.", "Section", "Division.1", "Dept..1", "Section.1"})
in
    #"Removed Columns1"

the final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Gianna,

What you can do is to replicate the steps yourself. The approach which I am suggesting is fully reliant on the stadrard commands in the PQ Editor (except a small tweak at step 5 below).

These are the steps:

1. Get, format and sanitise your data.

2. Identify a combination of columns that you are going to use as a matching set to define a substitution for the missing fields.

3. Identify what columns you need to substitute.

4. Group your table on those columns (defined in #2) using the Group By button on the Transform tab in the main menu:

Use Max on the columns that you've identified as substitutes in #3.

5.   Merge the tabe to itself, using a standard merge button on the Home tab of the main menu. Use the columns identified on step #2 as a driver/index. 

In the formula bar you will see somtthing like this:

Table.NestedJoin(#"Dictionary Table", {"Office", "Business Unit"}, #"Dictionary Table", {"Office", "Business Unit"}, "Custom1", JoinKind.LeftOuter)

Where #"Dictionary Table" above is the step immediatelly before the merge (which is now a currect step). In your case it will be something like #"Grouped Rows". You will need to change the FIRST appearance of this step name in the formula to the step preceeding it (i.e. the LAST step BEFORE grouping data at step 4). As a result you will have something like this:

Table.NestedJoin(#"Changed Type", {"Office", "Business Unit"}, #"Dictionary Table", {"Office", "Business Unit"}, "Custom1", JoinKind.LeftOuter)

Notice that step names before first and second appearance of {"Office", "Business Unit"} are now different.

6. Expand the merged columns.

7. Add as many columns as you need. The general idea is to check if an original field is empty and assign a value from the merged column. You can use add conditional column button on the AddColumn tab.

8. Remove redundant columns and rename new ones as required.

Hope this helps.

Cheers,

John