cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gstover
New Member

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

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. 

 

IDSupervisor IDOfficeBusiness UnitDivisionDept.Section
1231FrontX   
1423BackY   
1322BackY   
10FrontXADE
32BackYBCF
0 AllXAAA
20BackYBCF

 

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. 

 

IDSupervisor IDOfficeBusiness UnitDivisionDept.Section
1231FrontXADE
1423BackYBCG
1322BackYBCF
10FrontXADE
32BackYBCG
0 AllXAAA
20BackYBCF

 

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

2 ACCEPTED SOLUTIONS
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1669691145159.png


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.

View solution in original post

jbwtp
Memorable Member
Memorable Member

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:

jbwtp_0-1669760457235.png

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 

 

View solution in original post

8 REPLIES 8
v-yalanwu-msft
Community Support
Community Support

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.

vyalanwumsft_0-1669772820714.png

Then add condition column.

vyalanwumsft_1-1669772892604.pngvyalanwumsft_2-1669772906070.pngvyalanwumsft_3-1669772927173.png

delete other column. you could copy this step:

vyalanwumsft_4-1669773015380.png


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.

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

v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1669691145159.png


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 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
Memorable Member
Memorable Member

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

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
Memorable Member
Memorable Member

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:

jbwtp_0-1669760457235.png

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 

 

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors