Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to 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.
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
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.
Thank you! I was able to finally get it to work. I really appreciate your help.
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 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
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
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
Thank you! This was incredibly helpful. I was able to finally get it to work. I really appreciate your help.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
63 | |
58 | |
27 | |
17 | |
13 |