Forum Discussion
Solved
Returning column value if other column is unique using power query
Hi everyone, Below is an example of the table I am working with.. ID Number Passes Fails 123456 40 3 123456 40 ...
Do you want to identify the first occurrence of each ID number and return the value from the passes column? In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
Do you want to identify the first occurrence of each ID number and return the value from the passes column? In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
OliverScheurich Hi Oliver, thank you for the response. What you achieved on for the green table is exactly what I need, however when I tried recreating the steps in power query on my actual excel file, it doesn't group the table by the ID number. I'm not sure if this is because there are other queries that are taking place before I try grouping it.
Below is what I have already for my table in excel..
let
Source = Table.FuzzyNestedJoin(Merge1, {"Loan Number"}, Level_5_Data, {"Loan Number"}, "Level_5_Data", JoinKind.FullOuter, [IgnoreCase=true, IgnoreSpace=true]),
#"Expanded Level_5_Data" = Table.ExpandTableColumn(Source, "Level_5_Data", {"Loan Number", "Question Text", "Category Name", "Exception ID", "Exception Name", "Agree/Disagree", "Exception Status", "Exception Comments"}, {"Loan Number.2", "Question Text", "Category Name", "Exception ID", "Exception Name", "Agree/Disagree", "Exception Status", "Exception Comments"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Level_5_Data", each ([Audit Name] <> null)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Audit Name", "Loan Number", "Loan Status", "Loan Status Date", "Line of Business", "Sub Line of Business", "Sub Function", "Processor", "Processor SID", "Processor Manager", "Processor Manager Code", "Analyst", "Analyst SID", "Passes", "Not Applicable", "Total Answered", "Current Loan Status", "#Fails", "#Overturned", "#Resolved", "#Outstanding Fails", "Question Text", "Category Name", "Exception ID", "Exception Name", "Agree/Disagree", "Exception Status", "Exception Comments"})
in
#"Removed Other Columns"
and when I try to add in what you've shown me like this (please let me know if this does not look right)
let
Source = Table.FuzzyNestedJoin(Merge1, {"Loan Number"}, Level_5_Data, {"Loan Number"}, "Level_5_Data", JoinKind.FullOuter, [IgnoreCase=true, IgnoreSpace=true]),
#"Expanded Level_5_Data" = Table.ExpandTableColumn(Source, "Level_5_Data", {"Loan Number", "Question Text", "Category Name", "Exception ID", "Exception Name", "Agree/Disagree", "Exception Status", "Exception Comments"}, {"Loan Number.2", "Question Text", "Category Name", "Exception ID", "Exception Name", "Agree/Disagree", "Exception Status", "Exception Comments"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Level_5_Data", each ([Audit Name] <> null)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Audit Name", "Loan Number", "Loan Status", "Loan Status Date", "Line of Business", "Sub Line of Business", "Sub Function", "Processor", "Processor SID", "Processor Manager", "Processor Manager Code", "Analyst", "Analyst SID", "Passes", "Not Applicable", "Total Answered", "Current Loan Status", "#Fails", "#Overturned", "#Resolved", "#Outstanding Fails", "Question Text", "Category Name", "Exception ID", "Exception Name", "Agree/Disagree", "Exception Status", "Exception Comments"}),
group = Table.Group(Source, {"Loan Number"}, {"temp", each Table.AddIndexColumn(_, "Helper", 1, 1)}),
#"expanded temp" = Table.ExpandTableColumn(group, "temp", {"Passes", "#Fails", "Helper"}, {"Passes", "#Fails", "Helper"}),
#"added custom column" = Table.AddColumn(#"expanded temp", "Filtered Passes", each if [Helper] = 1 then [Passes] else 0),
#"removed columns" = Table.RemoveColumns(#"added custom column",{"Helper"})
in
#"Removed Other Columns"it does not add the additional "True Passes" column. Let me know if there's anything to fix this, thank you for the help!
let Source = Table.FuzzyNestedJoin(Merge1, {"Loan Number"}, Level_5_Data, {"Loan Number"}, "Level_5_Data", JoinKind.FullOuter, [IgnoreCase=true, IgnoreSpace=true]), #"Expanded Level_5_Data" = Table.ExpandTableColumn(Source, "Level_5_Data", {"Loan Number", "Question Text", "Category Name", "Exception ID", "Exception Name", "Agree/Disagree", "Exception Status", "Exception Comments"}, {"Loan Number.2", "Question Text", "Category Name", "Exception ID", "Exception Name", "Agree/Disagree", "Exception Status", "Exception Comments"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Level_5_Data", each ([Audit Name] <> null)), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Audit Name", "Loan Number", "Loan Status", "Loan Status Date", "Line of Business", "Sub Line of Business", "Sub Function", "Processor", "Processor SID", "Processor Manager", "Processor Manager Code", "Analyst", "Analyst SID", "Passes", "Not Applicable", "Total Answered", "Current Loan Status", "#Fails", "#Overturned", "#Resolved", "#Outstanding Fails", "Question Text", "Category Name", "Exception ID", "Exception Name", "Agree/Disagree", "Exception Status", "Exception Comments"}), #"group"= Table.Group(#"Removed Other Columns", {"Loan Number"}, {"temp", each Table.AddIndexColumn(_, "Helper", 1, 1)}), #"expanded temp" = Table.ExpandTableColumn(#"group", "temp", {"Passes", "#Fails", "Helper"}, {"Passes", "#Fails", "Helper"}), #"added custom column" = Table.AddColumn(#"expanded temp", "Filtered Passes", each if [Helper] = 1 then [Passes] else 0), #"removed columns" = Table.RemoveColumns(#"added custom column",{"Helper"}) in #"removed columns"You are welcome. Without seeing the file i can only guess that this code could work. Within the code each row has to refer to the row before and these are the only changes i can do without a file.
For example #"Expanded Level_5_Data" is the result of row 3 and it is used in the Table.SelectRows in row 4.
- Thank you again Oliver, this works and now creates the Filtered Passes column that I need. Is there a way where after running this query I am able to keep all of the other columns I have filtered for on line 4, so that they stay in the final table? Right now it is only the Loan Number, Passes, Fails, and Filtered Passes.. I tried adding the columns back in on line 7 but that did not seem to work. Thank you again