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

[gstover]

Hi All, 

I am hoping to be able to find a Power Query solution that will help me fill in blanks in my data based on a relationship to another column in other rows. I am working out of one table that currently looks something like the one below but with 285 rows, 96 of which have 3 columns blank. 

ID	Supervisor ID	Office	Business Unit	Division	Dept.	Section
123	1	Front	X
142	3	Back	Y
132	2	Back	Y
1	0	Front	X	A	D	E
3	2	Back	Y	B	C	F
0		All	X	A	A	A
2	0	Back	Y	B	C	F

I am wanting to say if Division, Dept., Section are blank, look at Supervisor ID, find it in ID column, and copy the Division, Dept., and Section from the appropriate row so that it looks something like the one below. 

ID	Supervisor ID	Office	Business Unit	Division	Dept.	Section
123	1	Front	X	A	D	E
142	3	Back	Y	B	C	G
132	2	Back	Y	B	C	F
1	0	Front	X	A	D	E
3	2	Back	Y	B	C	G
0		All	X	A	A	A
2	0	Back	Y	B	C	F

I am very new to Power BI so I appreciate any tips, tricks, or guidance in finding a potential solution. 

[v-yalanwu-msft · Accepted solution]

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.

[v-yalanwu-msft]

Hi, @gstover ;

First: you could add funtion.(here #"Changed Type" change to your Previous step.

= Table.NestedJoin(#"Changed Type", {"Supervisor ID"}, #"Changed Type", {"ID"}, "Custom1", JoinKind.LeftOuter)

Second expand it.

Then add condition column.

delete other column. you could copy this step:

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.

[gstover]

Thank you! I was able to finally get it to work. I really appreciate your help. 

[v-yalanwu-msft · Accepted solution]

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.

[gstover]

Hi Yalan Wu,  

Thank you for your response! I was able to add your code into a blank query and it did exactly what I needed. However, I have been struggling to get it to reference my actual data vs. the example data. Do you have any guidance for this? For reference, my table name is All - Capability Gaps.

Additionally, there are quite a few other columns involved in the table that I didn't think to include in the example data, could this be part of the issue? 

Thanks, 

Gianna

[jbwtp]

Hi @gstover,

this is not the most effective or impressive option, but this does hte job and is reasonably easy to follow/understand. The idea is to create a dictionary table (to define what values to use in case the data in the target columns is missing). Merge it to the original table and then create a final columns which would be either the original value of a target column (if it exists) or a substitute value from the merged dictionary table:

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 [D = _t, #"Supervisor ID" = _t, Office = _t, #"Business Unit" = _t, Division = _t, #"Dept." = _t, Section = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"D", Int64.Type}, {"Supervisor ID", Int64.Type}, {"Office", type text}, {"Business Unit", type text}, {"Division", type text}, {"Dept.", type text}, {"Section", type text}}),
    #"Dictionary Table" = Table.Group(#"Changed Type", {"Office", "Business Unit"}, {{"Division", each List.Max([Division]), type nullable text}, {"Dept.", each List.Max([#"Dept."]), type nullable text}, {"Section", each List.Max([Section]), type nullable text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Office", "Business Unit"}, #"Dictionary Table", {"Office", "Business Unit"}, "Custom1", JoinKind.LeftOuter),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Merged Queries", "Custom1", {"Division", "Dept.", "Section"}, {"Custom1.Division", "Custom1.Dept.", "Custom1.Section"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Custom1", "Division.Final", each if [Division] = null or [Division] = "" then [Custom1.Division] else [Division], type text),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Dept.Final", each if [#"Dept."] = null or [#"Dept."] = "" then [#"Custom1.Dept."] else [#"Dept."], type text),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Section.1", each if [Section] = null or [Section] = "" then [Custom1.Section] else [Section], type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Division", "Dept.", "Section", "Custom1.Division", "Custom1.Dept.", "Custom1.Section"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Division.Final", "Division"}, {"Dept.Final", "Dept."}, {"Section.1", "Section"}})
in
    #"Renamed Columns"

Kind regards,

John

[gstover]

Hi John, 

Thank you for your response! I was able to add your code into a blank query and it did exactly what I needed. However, I have been struggling to get it to reference my actual data vs. the example data. Do you have any guidance for this? For reference, my table name is All - Capability Gaps.

Additionally, there are quite a few other columns involved in the table that I didn't think to include in the example data, could this be part of the issue? 

Thanks, 

Gianna

[jbwtp · Accepted solution]

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 

[gstover]

Thank you! This was incredibly helpful. I was able to finally get it to work. I really appreciate your help.