Power BI – The key didn’t match any rows in the table

-> I was working on an existing Power BI desktop file and was utilizing a different SQL Server database than the one on which this Power BI file was created.

-> During the initial load I got below error,

The key didn’t match any rows in the table.

-> With this error, I clicked on “Transform Data” and check for this particular object,

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

-> I happened to check if the object dbo.Tbl_DisabledIndexes was present in the underlying database,

-> It seems like the object dbo.Tbl_DisabledIndexes is not present on the database. This Power BI Report when created based on a different database should have contained this table, but the current database doesn’t have it.

-> I corrected that by creating the table dbo.Tbl_DisabledIndexes and populated the appropriate rows into that SQL Server table.

-> Once the table was available, I reloaded the report and it worked as expected this time.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Power BI – Value should be a Currency

-> I was trying to create a new conditional column called “Price Tier” in Power BI based on a Currency column called Price. I got below message,

Value should be a Currency.

-> I couldn’t get much from the internet for this issue.

-> I ended up changing the currency column “Price” to decimal and then created the new conditional column “Price Tier”.

-> Once the new Conditional Column “Price Tier” was created. I changed “Price” back to Currency from Decimal.

-> Not sure if this is a bug, but the workaround actually worked for me.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Power BI – Change data source dynamically when opening Power BI report

-> I created a Power BI report on my development server that connected to a development SQL Server instance. I then moved onto a different server which doesn’t have connectivity to development SQL Server instance, when I opened and refreshed the report. I got below error,

Refresh
AlwaysON_Latency
Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)

-> Changing SQL Server data source dynamically when opening this Power BI report will help.

-> I performed below on the development server. Click on “Transform Data” in the report,

-> Created a new parameter for SQL Server Instance,

-> Click “OK”. Created another parameter for DatabaseName,

-> Both parameters are created,

-> Save the created parameter by clicking “Close & Apply”.

-> Click on “Transform Data” in the report again,

-> On the right side you will see “Query Settings”,

-> Click “settings” icon just next to Source,

-> You will see below dialog box,

-> Change the Server and database to use “Parameter” and select appropriate parameters as below,

-> Click “OK” and Save the changes by clicking “Close & Apply”.

-> Perform a Save as and save the file as a Power BI Template,

-> Now try opening the saved Power BI Template file and this time it will ask for SQL Server Instance and Database name,

-> From now on this file can be moved to other servers and can be connected to appropriate SQL Server instance and database.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.