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 3
122234 85 10
122234 85 10
101123 62 6
I am trying to create a new column within power query that would return the value in "Passes" IF the ID Number is unique, and return 0 if it is not unique. Something that might look like this..
ID Number Passes Fails Column
123456 40 3 40
123456 40 3 0
122234 85 10 85
122234 85 10 0
101123 62 6 62
Let me know if this is possible to do within the custom column formula in power query. Any help is appreciated, thank you.
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.
SQL
select *,iif(row_number() over ( partition by f01)=1,f02,0) column from fill_first_of_section order by rowid;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.
