The key didn't match any rows in the table. Does data model table order matter?

[garythomannCoGC]

power bi desktop, power query, expression error "The key didn't match any rows in the table"
Does the table order matter in relation to the data model when referenced in power query?
I believe it does from what I have found and wanting confirmation.

Screenshots to follow showing a working and non-working scenario.  The power query (m code, m language) is the same for both scenarios yet one errors and the other does not.

Fails

Works

Data model

Why is power query so fickle?   

Direct table reference works

[v-hashadapu]

Hi @garythomannCoGC , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[garythomannCoGC]

Thanks for the replies.  My first question still stands.  My first two screenshots show a change in order for a simple set of table declarations.  The order of declaration matters but why?  My third screenshot shows that the three tables are nicely related within the data model with findable keys.   Also same error if Assets is declared last.  We only have no error if Notifications table is declared last.  Why is it so :}

[v-hashadapu]

Hi @garythomannCoGC , Thank you for reaching out to the Microsoft Community Forum.

I think what’s really happening here is the difference between query order in the pane (which never matters) and the order of steps inside a single let in.. block. Power Query is lazy, it doesn’t run every step in sequence, it only evaluates what’s needed for the in … result. That means declaration order normally doesn’t matter, unless a step references something not yet defined or in your case, the step depends on a fragile {[Entity="…"]}[Data] lookup. Those lookups can behave inconsistently depending on which step ends up being evaluated, which is why things only worked when Notifications was last. It isn’t the order itself fixing it, it’s that the lookup only resolved cleanly in that context. The better approach is to avoid the {[Entity=…]} key altogether and reference the query/table directly so order stops being a factor.

[garythomannCoGC · Accepted solution]

@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 block

Here 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 :}

[garythomannCoGC]

Interesting.  In PQ as a dataflow we get a similar error about 'last step of your query'

This is the editor of the working version.  For the error above I have NotificationsIVMS higher up.

Using the 'Simplify output step' button we get 

=======================================================

pbi dataflow pq seems to have a mind of its own?
It will take the table references without error unlike pbi desktop pq but it does it own formatting.

My offline copy (ie using a real editor (not 'advanced')) goes from 

let
    // load tables
    Asset_table = DF_IVMS_Assets,
    WorkOrder_table = DF_IVMS_WorkOrders,
    NotificationIVMS = DF_IVMS_Notifications_IE,

    // Ignore columns with data of none or little value
    SelectColumns = Table.SelectColumns(NotificationIVMS, 
        {"Notification ID", "Notification Text", "Notification Type", "Status", "CC_ASSET_OBJNR", "Work Order ID", "Notification Date", 
         "Notif Date", "Notification Time", "Planner Group", "Fiscal Year", "Fiscal Period", "Created By", "Coding", 
         "Priority", "Priority Desc", "Completion Date", "Malfunction Start Date", "Malfunction End Date", "Breakdown Duration Hours", 
         "Total Days to Complete", "Total Days Overdue", "Settlement Receiver"}),

    // Work Order table merge inner join filter
    JoinWorkOrderFilter = Table.NestedJoin(
        SelectColumns, {"Work Order ID"}, 
        WorkOrder_table, {"Order Number"}, 
        "Work Order", 
        JoinKind.Inner),

    // Asset table merge inner join filter
    JoinAssetFilter = Table.NestedJoin(
        JoinWorkOrderFilter, {"CC_ASSET_OBJNR"}, 
        Asset_table, {"OBJNR"}, 
        "Asset", 
        JoinKind.Inner)
in
    JoinAssetFilter

to

let
    // load tables
    Asset_table = DF_IVMS_Assets,
    WorkOrder_table = DF_IVMS_WorkOrders,
    NotificationIVMS = DF_IVMS_Notifications_IE,
    // Ignore columns with data of none or little value
    SelectColumns = Table.SelectColumns(NotificationIVMS, {"Notification ID", "Notification Text", "Notification Type", "Status", "CC_ASSET_OBJNR", "Work Order ID", "Notification Date", "Notif Date", "Notification Time", "Planner Group", "Fiscal Year", "Fiscal Period", "Created By", "Coding", "Priority", "Priority Desc", "Completion Date", "Malfunction Start Date", "Malfunction End Date", "Breakdown Duration Hours", "Total Days to Complete", "Total Days Overdue", "Settlement Receiver"}),
    // Work Order table merge inner join filter
    JoinWorkOrderFilter = Table.NestedJoin(SelectColumns, {"Work Order ID"}, WorkOrder_table, {"Order Number"}, "Work Order", JoinKind.Inner),
    // Asset table merge inner join filter
    JoinAssetFilter = Table.NestedJoin(JoinWorkOrderFilter, {"CC_ASSET_OBJNR"}, Asset_table, {"OBJNR"}, "Asset", JoinKind.Inner),
    #"Remove columns" = Table.RemoveColumns(JoinAssetFilter, Table.ColumnsOfType(JoinAssetFilter, {type table, type record, type list, type nullable binary, type binary, type function}))
in
    #"Remove columns"

 and added bonus step #"Remove columns" too.

Reformatting by pq only happens if it decides to add its own step.

We also have a warning for the SelectColumns step?

========================================================

Simplifying the pq code we have 

// simplify to remove SelectColumns warning and remove indirect references to tables
let
    Source = Notifications_IE,

    // Work Order table merge inner join filter
    JoinWorkOrderFilter = Table.NestedJoin(
        Source, {"Work Order ID"}, 
        DF_IVMS_WorkOrders, {"Order Number"}, 
        "Work Order", 
        JoinKind.Inner),

    // Asset table merge inner join filter
    JoinAssetFilter = Table.NestedJoin(
        JoinWorkOrderFilter, {"CC_ASSET_OBJNR"}, 
        DF_IVMS_Assets, {"OBJNR"}, 
        "Asset", 
        JoinKind.Inner)
in
    JoinAssetFilter

and on save pq decides to add the remove columns step

let
    Source = DF_IVMS_Notifications_IE,
    // Work Order table merge inner join filter
    JoinWorkOrderFilter = Table.NestedJoin(Source, {"Work Order ID"}, DF_IVMS_WorkOrders, {"Order Number"}, "Work Order", JoinKind.Inner),
    // Asset table merge inner join filter
    JoinAssetFilter = Table.NestedJoin(JoinWorkOrderFilter, {"CC_ASSET_OBJNR"}, DF_IVMS_Assets, {"OBJNR"}, "Asset", JoinKind.Inner),
    #"Remove columns" = Table.RemoveColumns(JoinAssetFilter, Table.ColumnsOfType(JoinAssetFilter, {type table, type record, type list, type nullable binary, type binary, type function}))
in
    #"Remove columns"

which is needed to trim down the inner joined columns after rows reduction, well down dataflow pq :}

[v-hashadapu]

Hi @garythomannCoGC , hope you are doing well. We really appreciate the insights pointed out, this will help others with similar queries clear out their issues. If you have any other queries, please feel free to create new posts, we are always happy to assist.

Thanks for your continued support in the community.

[v-hashadapu]

Hi @garythomannCoGC , Thank you for reaching out to the Microsoft Community Forum.

Table order in Power Query doesn’t matter. The error happens because {[Entity="Work Order"]}[Data] can’t find an exact match in the dataflow. This is usually caused by a small difference in the entity name or pulling from the wrong workspace. The fix is to use the exact name from Navigator or better, just reference the existing query (#"Work Order"). You can also use try … otherwise so the query won’t break if the dataflow changes.

[VipinKumar]

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.