cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nleuck
Post Patron
Post Patron

Expression Error: The key didn't match any rows in the table

Hello All,

 

I have a date table that is a Direct Query to my SQL Server. The table only went to 12-31-2020. So I had the owner of the table update the table. Now the table runs through 11-07-2032, but this is causing errors for my YTD measures. I'm getting Blank values in my visuals.

 

I went to transform data and selected the Source for my date table. In the Advanced options I pasted the following SQL statement:

 

SELECT
 list of columns
FROM database.schema.tablename
WHERE [Date] <= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1)

 

The data loads just find. Basically I'm trying to get all the dates before the end of the current year, but a yellow triangle pops up next to the date table. Here is the following error:

 

Expression Error the key didn't match any rows in the table.png

Any ideas on how to fix this?

 

Thanks,

1 ACCEPTED SOLUTION
parry2k
Super User III
Super User III

@nleuck that's your issue, can you paste the M code here. Why not remove the navigation step, I don't think you need it since you are adding the SQL query directly.

 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

6 REPLIES 6
parry2k
Super User III
Super User III

@nleuck that's your issue, can you paste the M code here. Why not remove the navigation step, I don't think you need it since you are adding the SQL query directly.

 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

@parry2k 

 

Thank you! It seems that removing the Navigation step fixed it.

parry2k
Super User III
Super User III

@nleuck is that error coming on the source step or in the following steps?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k 

 

I have Source then Navigation. The error is at Navigation.

parry2k
Super User III
Super User III

@nleuck can you check if you can run the same SQL statement in SSMS?

 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k 

 

It runs in SQL and returns the results I want.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors