Forum Discussion
Filter one column by value, create unique list and compare with another column to find non-matching
Hello,
I have the following table with submissions sent every week (by week number) by a group of people who send their individual scores.
| Week Number | Name | Submitted Score |
| 42 | Anahi Johns | 58 |
| 42 | Anahi Johns | 38 |
| 42 | Max Gilbert | 43 |
| 43 | Trenton Espinoza | 62 |
| 43 | Trenton Espinoza | 30 |
| 44 | Max Gilbert | 78 |
And this is the reference list of all names who should submit their scores every week:
| Reference List of names |
| Anahi Johns |
| Trenton Espinoza |
| Max Gilbert |
I am trying to create a new table in Power Query that contains the list of names (compared to the reference list of names) who have not submitted by week number. Notice that for any week number, the same person can submit their scores more than one time.
The new table should have two columns. For example, considering the two tables above, the result should be:
| Week Number | Name |
| 42 | Trenton Espinoza |
| 43 | Anahi Johns |
| 43 | Max Gilbert |
| 44 | Anahi Johns |
| 44 | Trenton Espinoza |
Both tables are dynamic since the rows are being added at any time, so the result should always recalculate to show the actual results.
Any help is much appreciated!!
I will assume your two tables are named Reference and Submissions.
Create a copy of your Reference table and add a custom column defined as
{List.Min(Submissions[Week Number])..List.Max(Submissions[Week Number])}Expand this column to new rows to get a full cross product of names and weeks.
Then do a left anti-join merge with the Submissions table matching on both the Name and Week Number columns.
Remove the extra column of nulls.
Full code:
let Source = Reference, #"Added Custom" = Table.AddColumn(Reference, "Week Number", each {List.Min(Submissions[Week Number])..List.Max(Submissions[Week Number])}), #"Expanded Week Number" = Table.ExpandListColumn(#"Added Custom", "Week Number"), #"Merged Queries" = Table.NestedJoin(#"Expanded Week Number", {"Name", "Week Number"}, Submissions, {"Name", "Week Number"}, "Submissions", JoinKind.LeftAnti), #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Submissions"}) in #"Removed Columns"
5 Replies
I will assume your two tables are named Reference and Submissions.
Create a copy of your Reference table and add a custom column defined as
{List.Min(Submissions[Week Number])..List.Max(Submissions[Week Number])}Expand this column to new rows to get a full cross product of names and weeks.
Then do a left anti-join merge with the Submissions table matching on both the Name and Week Number columns.
Remove the extra column of nulls.
Full code:
let Source = Reference, #"Added Custom" = Table.AddColumn(Reference, "Week Number", each {List.Min(Submissions[Week Number])..List.Max(Submissions[Week Number])}), #"Expanded Week Number" = Table.ExpandListColumn(#"Added Custom", "Week Number"), #"Merged Queries" = Table.NestedJoin(#"Expanded Week Number", {"Name", "Week Number"}, Submissions, {"Name", "Week Number"}, "Submissions", JoinKind.LeftAnti), #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Submissions"}) in #"Removed Columns"AlexisOlson Thanks!
But i have another question based on the same resulting merged table. So, this is the result of the merged table, named: No Match Names
Name Week Number Trenton Espinoza 42 Anahi Johns 43 Max Gilbert 43 Anahi Johns 44 Trenton Espinoza 44
How do i compare this merged table above with another table named Exception Names (as below) by Name and Week Number and highlight the Names in the No Match Names table which are missing or not common for that Week Number (highlight font in red) or common for the same Week Number but based on the condition that the Week Number in the Exception Names table below should be equal to the Week Number in the No Match Names table (highlight font in orange).Name Week Number Jadyn Levine 43 Trenton Espinoza 41 Anahi Johns 43 Katherine Waters 45 Trenton Espinoza 43 Jazlynn Mcclure 46 Anahi Johns 47 Everett Lang 42 Anahi Johns 44 My guess is that this could be done in Power Query or DAX but i am new to both and i don't know which is the best way. But as of now, i have these two tables in Power BI in two table visualizations.
I don't quite follow. What do you expect the result to be?
I have these two tables (both in Power Query) and displayed in Power BI as two Table visualizations:
Table name: Submissions
Name Week Number Trenton Espinoza 42 Anahi Johns 43 Max Gilbert 43 Jadyn Levine 44 Table name: Exceptions
Name Week Number Jadyn Levine 43 Trenton Espinoza 41 Anahi Johns 43 Katherine Waters 45 Jazlynn Mcclure 46 In Power BI, i want the result to be shown in the Table Submissions by highlighting the names based on certain conditions.
Result shown in Power BI in the Table Submissions:
Name Week Number Trenton Espinoza 42 Anahi Johns 43 Max Gilbert 43 Jadyn Levine 44 The conditions for highlighting the names in the table Submissions:
1. The names in the Submissions table not in common with the names in the Exceptions table, highlight in red.
2. If the names in the Submissions table is common with the names in the Exceptions table, then check if the Week Number in the Submissions table is less than the Week Number in the Exceptions table. If no, then highlight in red.
But i would like this to colour change in the table visualization in the Power BI report.