Expression.Error: The key didn't match any rows in the table. Details: Key= Schema=dbo Item=vFactSales Table=[Table]

Question

Expression.Error: The key didn't match any rows in the table. Details: Key= Schema=dbo Item=vFactSales Table=[Table]

student training 5

The AdventureWorksDW20XX.pbix Power BI lesson file for the Exercises has no vFactSales table in the data model so the "Sales" and "Sales Order" tables do not display data when looked at in the Transform data dialog box, as they reference a non-existent table, (as seen in the Home>Query>Advanced Editor)

dbo_vFactSales = Source{[Schema="dbo",Item="vFactSales"]}[Data],

therefore producing error:

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

Details:

Key=

    Schema=dbo

    Item=vFactSales

Table=[Table]

This question is related to the following Learning Module

Syed Saleem Peera 13,060 Reputation points Microsoft External Staff Volunteer Moderator

2024-06-18T10:39:07.23+00:00

Hi student training,

Thank you for reaching out to us on Microsoft Q&A forum.

Can you please specify the exact Module name/Exercise link where you are encountering this error along with the screenshot for reference.

Thank you.
student training 5 Reputation points

2024-06-18T23:09:59.6166667+00:00

Hi Syed Saleem Peera,

Thank you for responding.

https://learn.microsoft.com/en-us/training/modules/enforce-power-bi-model-security/4a-exercise-enforce-model-security

it appears any AdventureWorksDW20XX.pbix can reproduce error message in Power BI's *Home>Queries>*Transform data.

Thank you.

1 answer

Your answer

Syed Saleem Peera 13,060 Reputation points Microsoft External Staff Volunteer Moderator

2024-06-18T10:39:07.23+00:00

Hi student training,

Thank you for reaching out to us on Microsoft Q&A forum.

Can you please specify the exact Module name/Exercise link where you are encountering this error along with the screenshot for reference.

Thank you.
student training 5 Reputation points

2024-06-18T23:09:59.6166667+00:00

Hi Syed Saleem Peera,

Thank you for responding.

https://learn.microsoft.com/en-us/training/modules/enforce-power-bi-model-security/4a-exercise-enforce-model-security

it appears any AdventureWorksDW20XX.pbix can reproduce error message in Power BI's *Home>Queries>*Transform data.

Thank you.

Answer 1

Syed Saleem Peera 13,060 Microsoft External Staff Volunteer Moderator

Hi student training,

Thank you for reaching out to us on Microsoft Q&A forum.

We tried from our end; it's working fine for us. Kindly refer the below screenshots.

"Clear the browser history and start again. This will remove all traces of your previous activity, allowing you to browse with a fresh start."

User's image

If you are still facing any issue, please let us know in the comments. We are glad to help you.

If the information is helpful, please Accept Answer so that it would be helpful to community members.

Thank you.

Syed Saleem Peera 13,060 Reputation points Microsoft External Staff Volunteer Moderator

2024-06-20T05:08:56.3533333+00:00

Hi student training,

Just checking in to see if you had a chance to see the previous answer. Please let me know if it was helpful and don't hesitate to reach out to us if you face any issue.

If the information is helpful, please Accept Answer so that it would be helpful to community members.

Thank you.
Syed Saleem Peera 13,060 Reputation points Microsoft External Staff Volunteer Moderator

2024-06-21T03:44:33.09+00:00

Hi student training,

I wanted to check if you had the opportunity to review the information which was provided in our previous answer. Please feel free to contact us if you have any more quires. If you feel that your quires have been resolved, please accept the answer by clicking the "Upvote" and "Accept Answer" on the post.

Thank you.
student training 5 Reputation points

2024-06-22T15:54:38.41+00:00

Hi Syed Saleem Peera,

It works fine on the virtual machine but not outside the virtual machine on other computers. It does not matter if using the AdventureWorksDW2020.pbix nor AdventureWorksDW2022.pbix file. In order to understand how the pbix file is put together requires not using the virtual machine. The lessons/exercises that use the Sales Analysis pbix file work/do not work similarly just like the AdventureWorksDW20XX pbix lessons/exercises.Thank you.
Syed Saleem Peera 13,060 Reputation points Microsoft External Staff Volunteer Moderator

2024-06-24T06:27:08.5633333+00:00

Hi student training,

Thank you for your patience.

It should work outside the virtual machine as well. If you are facing any issues, please share a screenshot of the error to assist you better.

Please feel free to contact us if you have any more quires. If you feel that your quires have been resolved, please accept the answer by clicking the "Upvote" and "Accept Answer" on the post.

Thank you.
Michael Deschambault 30 Reputation points

2024-08-13T14:53:07.21+00:00

Hello,

I am running into the same issue with this PBIX and it is because the vFactSales view doesn't exist in the database. I have also installed 2019 and 2017 variants of the Warehouse and its not in any of those either. Without the view, users/learners have no way to determine what tables and logic is being used to replicate the view used in the PBIX.

Is anyone able to share the "create view" for vFactSales if they have it please. Thanks in advance.
Syed Saleem Peera 13,060 Reputation points Microsoft External Staff Volunteer Moderator

2024-08-14T12:35:55.7566667+00:00

Hi Michael Deschambault,

Thank you for reaching out to us on Microsoft Q&A forum.

We tried from our end; it's working fine for us. Kindly refer the below screenshots.

"Clear the browser history and start again. This will remove all traces of your previous activity, allowing you to browse with a fresh start." Please feel free to contact us if you have any more quires. If you feel that your quires have been resolved, please accept the answer by clicking the "Upvote" and "Accept Answer" on the post.

Thank you.

Michael Deschambault 30

Hi @Syed Saleem Peera ,

What happens when you attempt to refresh the datasets from your SQL Source? Can you provide a screenshot of your SQL Instance and specifically the view for vFactSales?

For me and I believe the Author of this post, the issue is when I update the server connections in the pbix to my SQL instance (local, azure, vm, etc), the queries in the pbix for "sales" and "sales order" fail because the vFactSales view doesn't exist or isn't included in any of the current AdventureWorks DW versions available for download. The other queries complete with no issue because the tables exist.

I resolved the missing vFactSales view for my instance by creating it in the AdventureWorks DW with what I believe the logic is and it works in the pbix report. I'll post the SQL below but please keep in mind this is my interpretation of what I think the view is, not what might actually be used in the Power BI Author's version or what the Author has in their AdventureWorks SQL database. There may be additional transformations that I am not aware of and did not apply to this view.

USE [AdventureWorksDW2022]
GO

/****** Object:  View [dbo].[vFactSales]    Script Date: 2024-08-14 6:54:27 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vFactSales] 
AS
SELECT 
	  'Internet' AS Channel	
	  ,[ProductKey]
      ,[OrderDateKey]
      ,[DueDateKey]
      ,[ShipDateKey]
      ,[CustomerKey]
	  ,NULL AS ResellerKey
      ,[PromotionKey]
      ,[CurrencyKey]
      ,[SalesTerritoryKey]
	  ,NULL AS EmployeeKey
	  ,CONCAT(SalesOrderNumber,' - ', SalesOrderLineNumber) AS SalesOrderLineKey
      ,[SalesOrderNumber]
      ,[SalesOrderLineNumber]
      ,[RevisionNumber]
      ,[OrderQuantity]
      ,[UnitPrice]
      ,[ExtendedAmount]
      ,[UnitPriceDiscountPct]
      ,[DiscountAmount]
      ,[ProductStandardCost]
      ,[TotalProductCost]
      ,[SalesAmount]
      ,[TaxAmt]
      ,[Freight]
      ,[CarrierTrackingNumber]
      ,[CustomerPONumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
  FROM [AdventureWorksDW2022].[dbo].[FactInternetSales]

  UNION

  SELECT 
  	  'Reseller' AS Channel
	  ,[ProductKey]
      ,[OrderDateKey]
      ,[DueDateKey]
      ,[ShipDateKey]
	  ,NULL AS CustomerKey
      ,[ResellerKey]
      ,[PromotionKey]
      ,[CurrencyKey]
      ,[SalesTerritoryKey]
      ,[EmployeeKey]
	  ,CONCAT(SalesOrderNumber,' - ', SalesOrderLineNumber) AS SalesOrderLineKey
      ,[SalesOrderNumber]
      ,[SalesOrderLineNumber]
      ,[RevisionNumber]
      ,[OrderQuantity]
      ,[UnitPrice]
      ,[ExtendedAmount]
      ,[UnitPriceDiscountPct]
      ,[DiscountAmount]
      ,[ProductStandardCost]
      ,[TotalProductCost]
      ,[SalesAmount]
      ,[TaxAmt]
      ,[Freight]
      ,[CarrierTrackingNumber]
      ,[CustomerPONumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
  FROM [AdventureWorksDW2022].[dbo].[FactResellerSales]

GO

Michael Deschambault 30

Hello @Syed Saleem Peera

What happens when you attempt to refresh the queries in the pbix? Can you provide a screenshot of your sql instance (source) and specifically the vFactSales view?

For me and I believe the author, the vFactSales view is missing from the source database. It appears to be missing from all of the current AdventureWorksDW versions available for download and what ends up happening with the sales and sales order view is they fail when a refresh is attempted because the view doesn't exist.

I resolved this by creating the view with what I believe is required. This may not mirror the author of the Power BI Report because the view they are using may have additional logic or transformations included that I am not aware of; however it works for me in this specific report.

I've included the SQL below to create the view for anyone that is also missing this from their downloaded version of AdventureWorks.

USE [AdventureWorksDW2022]
GO

/****** Object:  View [dbo].[vFactSales]    Script Date: 2024-08-14 6:54:27 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vFactSales] 
AS
SELECT 
	  'Internet' AS Channel	
	  ,[ProductKey]
      ,[OrderDateKey]
      ,[DueDateKey]
      ,[ShipDateKey]
      ,[CustomerKey]
	  ,NULL AS ResellerKey
      ,[PromotionKey]
      ,[CurrencyKey]
      ,[SalesTerritoryKey]
	  ,NULL AS EmployeeKey
	  ,CONCAT(SalesOrderNumber,' - ', SalesOrderLineNumber) AS SalesOrderLineKey
      ,[SalesOrderNumber]
      ,[SalesOrderLineNumber]
      ,[RevisionNumber]
      ,[OrderQuantity]
      ,[UnitPrice]
      ,[ExtendedAmount]
      ,[UnitPriceDiscountPct]
      ,[DiscountAmount]
      ,[ProductStandardCost]
      ,[TotalProductCost]
      ,[SalesAmount]
      ,[TaxAmt]
      ,[Freight]
      ,[CarrierTrackingNumber]
      ,[CustomerPONumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
  FROM [AdventureWorksDW2022].[dbo].[FactInternetSales]

  UNION

  SELECT 
  	  'Reseller' AS Channel
	  ,[ProductKey]
      ,[OrderDateKey]
      ,[DueDateKey]
      ,[ShipDateKey]
	  ,NULL AS CustomerKey
      ,[ResellerKey]
      ,[PromotionKey]
      ,[CurrencyKey]
      ,[SalesTerritoryKey]
      ,[EmployeeKey]
	  ,CONCAT(SalesOrderNumber,' - ', SalesOrderLineNumber) AS SalesOrderLineKey
      ,[SalesOrderNumber]
      ,[SalesOrderLineNumber]
      ,[RevisionNumber]
      ,[OrderQuantity]
      ,[UnitPrice]
      ,[ExtendedAmount]
      ,[UnitPriceDiscountPct]
      ,[DiscountAmount]
      ,[ProductStandardCost]
      ,[TotalProductCost]
      ,[SalesAmount]
      ,[TaxAmt]
      ,[Freight]
      ,[CarrierTrackingNumber]
      ,[CustomerPONumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
  FROM [AdventureWorksDW2022].[dbo].[FactResellerSales]

GO

Syed Saleem Peera 13,060 Reputation points Microsoft External Staff Volunteer Moderator

2024-08-15T03:45:52.2233333+00:00

Hi Michael Deschambault,

Thank you for your patience.

I successfully refreshed the report without any errors. Please refer to the screenshot below for the source vFactSales table.

Thank you.
student training 5 Reputation points

2024-09-26T16:27:36.78+00:00

Why does the Virtual Machine (VM) Power BI work to see the Sales and Sales Order tables when they are both looking for a non-existent vFactSales table from the database? In the SQL Server Management Studio (SSMS) on the VM there is no dbo.FactSales table like the other tables in Power BI see?

What there is is a View called dbo.vFactSales under Views folder not Tables folder, but that is not in the downloaded database file - there are no Views in the downloadable file:

In Power BI Source Applied Step sees no FactSales table in existence from the Database, yet Power Query Editor in Power BI on the VM can populate the Sales table from a non-existent FactSales table because line 30 has a vFactSales Table View that is not in the downloaded file?
student training 5 Reputation points

2024-09-26T16:35:38.39+00:00

the SQL Server Management Studio database shows no dbo.FactSales under Tables folder but shows dbo.vFactSales under Views folder. That is why Power BI works on the Virtual Machine and not one one's own machine since the downloaded files have no Views under Views folder.

^ Virtual Machine. / Own machine with downloaded Files and No Views
Michael Deschambault 30 Reputation points

2024-09-26T16:40:47.89+00:00

I addressed the vFactSales not being included in current downloads of the database and provided a SQL script to create the view. See my previous comments.
student training 5 Reputation points

2024-09-26T16:53:44.0266667+00:00

Since the downloaded lesson files have no Views in them, they need to be created manually? somehow for Power Bi to reference a not-yet-existing dbo.vFactSales view.
student training 5 Reputation points

2024-09-26T20:37:22.36+00:00

Thank you Michael Deschambault. To find where to execute that script, in SQL Server Management Studio & looking under the AdventureWorksDW database, when I right -click Views folder>New View... and paste your SQL script, SSMS gives an error. No other places seems to be to create a New View.?

So apparently that is not the way to create a View in SQL Server Management Studio.? Or it is but it cannot accept that SQL script in that location?.Once I corrected the three 2022 to 2020 (since that the database I have), and tried saving it as vFactSales, error appeared.

Making CREATE VIEW the first line does not help as the error implies it should nor if I delete everything before the first SELECT and after the last FROM, then another error appears
Michael Deschambault 30 Reputation points

2024-09-26T20:55:51.2466667+00:00

Just open a new query window, paste the full scrit into the empty query window and execute the sql. It will automatically create it.
Syed Saleem Peera 13,060 Reputation points Microsoft External Staff Volunteer Moderator

2024-09-30T13:55:59.7533333+00:00

Hi Michael Deschambault,

If it is a general discussion related to the Power Platform, please use this forum.https://community.fabric.microsoft.com/t5/Power-BI-forums/ct-p/powerbi

Please feel free to contact us if you have any more quires. If you feel that your quires have been resolved, please accept the answer by clicking the "Upvote" and "Accept Answer" on the post.

Thank you.

Frank Geisler 21 MVP

Hi!

The problem is that in AdventureWorksDW2022 the view dbo.vFactSales is missing. Here is a script that will create the view so that the Power BI Report will work again:

DROP VIEW dbo.vFactSales;
GO
CREATE VIEW dbo.vFactSales
AS
SELECT
	SalesOrderLineNumber
  , SalesOrderNumber
  , ResellerKey
  , SalesOrderLineNumber + ProductKey AS SalesOrderLineKey
  , NULL AS CustomerKey
  , ProductKey
  , OrderDateKey
  , DueDateKey
  , ShipDateKey
  , SalesTerritoryKey
  , OrderQuantity
  , UnitPrice
  , ExtendedAmount
  , ProductStandardCost
  , TotalProductCost
  , SalesAmount
  , UnitPriceDiscountPct
  , 'Reseller' AS Channel
FROM
	dbo.FactResellerSales
UNION ALL
SELECT
	SalesOrderLineNumber
  , SalesOrderNumber
  , SalesOrderLineNumber + ProductKey AS SalesOrderLineKey
  , NULL AS ResellerKey
  , CustomerKey
  , ProductKey
  , OrderDateKey
  , DueDateKey
  , ShipDateKey
  , SalesTerritoryKey
  , OrderQuantity
  , UnitPrice
  , ExtendedAmount
  , ProductStandardCost
  , TotalProductCost
  , SalesAmount
  , UnitPriceDiscountPct
  , 'Internet' AS Channel
FROM
	dbo.FactInternetSales;

Shankar Chatterjee 0 Reputation points

2024-11-26T05:59:05.62+00:00

Hello @Michael Deschambault thanks for the code to create the missing view. I downloaded AdventureWorksDW2022 and it was missing the view. After creating the view, all queries got applied and I could complete this learning: https://microsoftlearning.github.io/mslearn-fabric/Instructions/Labs/15-work-with-model-relationships.html. Thanks a bunch.

Share via

Expression.Error: The key didn't match any rows in the table. Details: Key= Schema=dbo Item=vFactSales Table=[Table]

1 answer

Your answer