Forum Discussion
Impactful IndividualThe key didn't match any rows in the table. Does data model table order matter?
v-hashadapu Thanks. I get the gist of what you have said one point for trying.
Still doesn't make logical sense though.
Now I get that the order of steps visually are not processed by the Power Query engine in any particular order. But it is only one let .. in blockHere is the first Fails screenshot again including the whole screen so we can see the steps on the rhs.
More screenshots with the 'table' declarations out of order with NotificationsIE moving up till we get a possible error.Now lets try and break it :}
This begs the question why the difference between the
" SelectColumns = Table.SelectColumns(NotificationIE, { ... " statement and the Table.NestedJoin 's ?Broken, essentially the same as the first fails screenshot. We now reference Assets indirectly even though NotificationsIE has an indirect reference that works.
Now lets move steps around illogically and see what happens.
Works but record count for NotificationsIE is back to 1.7m and not the 1.2k expected.
So order does matter :}
The "Expression.Error: The key didn't match any rows in the table" error is a common symptom of this issue.
Based on the screenshots provided, I can say,
The M code itself is not the problem, the issue is how Power Query executes the steps in sequence. When you perform a merge, the first table you select acts as the primary table and the second table acts as the secondary table.
If the primary table contains a key that doesn't exist in the secondary table, and you are trying to apply a filter or a transform based on that key in a later step, it can cause an error. This is exactly what's happening in your non-working scenario.
Working Scenario:
In the working scenario, the 'Notification (IE)' table is referenced first in the merge with the 'Work Order' table, and then the result is merged with the 'Asset' table.
The first merge (Work Order into Notification) is a left outer join.
The second merge(Asset into Notification) is also a left outer join.
In this sequence, the Notification table is the primary table. The keys from Notification are used to look up matching rows in Work Order and Asset. Since not every Notification has a matching Work Order or Asset, a left outer join will simply return nulls for those rows, which is a valid result and won't cause an error. This is a common and correct pattern for building a fact-to-dimension relationship in a star schema.
Non-Working Scenario:
In the non-working scenario, the M code for the merge steps is the same, but the order of the tables in the data model and the subsequent execution of the query steps appear to be the cause of the error. The error "The key didn't match any rows in the table" often indicates that a previous step created a filtered or transformed table where the key you're trying to merge on no longer exists.
While the M code you provided for both scenarios is identical, the sequence of applied steps can be different, which is what ultimately dictates the outcome. Power Query executes each step in order from top to bottom. If a previous step filters or changes a table in a way that removes a key needed for a later merge, the merge will fail.
For instance, if the Work Order table were to be filtered on Notification (IE) and the key Work Order ID did not exist in the now-filtered Work Order table, then the subsequent merge would fail.
Note: The order of tables matters in Power Query when performing merges, especially when dealing with scenarios where keys may not be present in all tables. While the M code syntax is the same, the data context and the sequence of operations applied to the tables are critical.