Report

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

~ Vitalie Ciobanu ~ 4 Comments

Error:

Refreshing Power BI report generates “The key didn’t match any rows in the table” error.

Cause:

Click “Edit Queries” button. If you do not see the error message, click “Refresh Preview” button. Once you have the error message, click “Go To Error“.

Error message will remain, but you should have “Edit Settings” button now, click it.

In the next screen, Navigation, you should see what exactly Power BI is trying to access but cannot do so.

In my example, I have an Excel file as my source. When I first connected to this data source, my Table name was Table1. Yesterday I changed where my Excel file gets data from and this, in turn, changed Excel’s Table name to “report“.

Obviously, when Power BI tries to refresh the data, it cannot find Table1 table anymore.

Solution:

In the same Navigation screen, selecting my new Table name “report” will fix the issue assuming all other columns in the Excel file are the same.

Same is true for any data source, not only Excel file. Follow same steps to identify what’s causing the error and then fix it as needed.

Create a report to count all computer models and manufacturer for computers inside a specific collection

~ Vitalie Ciobanu

SELECT    dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Manufacturer, dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model,
                      COUNT(dbo.v_GS_COMPUTER_SYSTEM.Model0) AS Total
FROM         dbo.v_GS_COMPUTER_SYSTEM INNER JOIN
                      dbo.v_FullCollectionMembership ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_FullCollectionMembership.ResourceID
GROUP BY dbo.v_GS_COMPUTER_SYSTEM.Model0, dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, dbo.v_FullCollectionMembership.CollectionID
HAVING      (dbo.v_FullCollectionMembership.CollectionID = ‘CollectionID’)
ORDER BY Total DESC, Manufacturer, Model

Upgrade Configuration Manager client

~ Vitalie Ciobanu

Recently I reinstalled ConfigMgr server in my company and was looking for an easy method to upgrade all Configuration Manager clients to 4.00.6487.2000 version.
Put some notes on the table and finally made the following plan:

  • Create a report that counts all client versions. (This is optional, just for informational purposes).
    Report query is:SELECT TOP (100) PERCENT Client_Version0 AS [ConfigMgr client version], COUNT(Client_Version0) AS Total
    FROM dbo.v_R_System GROUP BY Client_Version0, Client0 HAVING (Client0 = 1)
    ORDER BY Total DESC, [ConfigMgr client version]
  • Create a collection (“Older Clients” for example) with all system resources with a client version not 4.00.6487.2000.
    Collection query is:SELECT SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client
    FROM SMS_R_System
    WHERE SMS_R_System.ClientVersion != "4.00.6487.2000"

    This way, system resources with older client version will be members of this collection.

  • Created a package and program with ConfigMgr client upgrade with custom command line parameters.
    Program command line is:CCMSETUP.EXE /noservice SMSSITECODE=CFM SMSCACHESIZE=1024 SMSSLP=BLAH.DOMAIN.COM SMSMP=BLAH.DOMAIN.COM RESETKEYINFORMATION=TRUE
  • Advertised it to “Older Clients” collection.

Now, as system resources with older client version are members of this collection they will receive the advertisement and will silently install the latest ConfigMgr client.

When the collection will have no system resources, I will know that all clients are upgraded. Also, I can check this by opening the same report from any browser on any computer.

All reports with links to other reports

~ Vitalie Ciobanu

Last month, after restoring the only ConfigMgr site and importing new reports, I saw that none of the reports I’m using have a link to another report.

To be sincere, this was a problem I didn’t know where to start troubleshooting. Thanks to guys from Microsoft’s ConfigMgr forum, who helped me identify the problem and recommended some troubleshooting steps.

So, the problem was due the fact that none of my reports had a link to another report. Don’t know how this happened… Anyway, to resolve this issue, I quickly installed a ConfigMgr site in a virtual machine and using Tom Watson’s advice, created a query that lists all reports with links to other reports. The query is something like this:
SELECT     TOP (100) PERCENT ReportID, Name, Category, DrillThroughReportID
FROM       dbo.v_Report
WHERE      (NOT (DrillThroughReportID IS NULL))
ORDER BY   ReportID
and the result looks like this:

DrillThroughReportID tells the ReportdID of the linked report. Basically, in a default configuration, I know the original ReportID and the linked ReportID.

Now, knowing these info, I’m manually creating the links in my production environment. This is a long process indeed, but I couldn’t find a way to automate this.

Create collection with all Windows XP SP 1-2-3 systems

~ Vitalie Ciobanu

To create a collection with all Windows XP SP1 systems, use the query bellow:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Caption like "%Windows XP%" and SMS_G_System_OPERATING_SYSTEM.CSDVersion = "Service Pack 1"

Similarly, to create a collection with all Windows XP SP2 systems, just change the Service Pack version number:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Caption like "%Windows XP%" and SMS_G_System_OPERATING_SYSTEM.CSDVersion = "Service Pack 2"

And the same for all Windows XP SP3 systems:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Caption like "%Windows XP%" and SMS_G_System_OPERATING_SYSTEM.CSDVersion = "Service Pack 3"

All .mp3 files on Desktop and My Documents folder

~ Vitalie Ciobanu

Want to know if your users store mp3 files on Desktop or My Documents folders?

  1. Add .mp3 file type to Software Inventory Client Agent properties
      1. Navigate to Site Database – Site Management – Site Name – Site Settings – Client Agents and double click Software Inventory Client Agent
      2. Select Inventory Collection tab
      3. Click the new button and type *.mp3 as a file name.
    1. Set the location: All client hard disks or Variable or path name
    2. Click OK to close all open windows
  2. Wait for Software Inventory to run and collect data
  3. Create a report using the following query: SELECT TOP (100) PERCENT SYS.Netbios_Name0, SF.FileName, SF.FileSize / 1024 AS [Size, KB], SF.FilePath FROM dbo.v_GS_SoftwareFile AS SF INNER JOIN dbo.v_R_System AS SYS ON SYS.ResourceID = SF.ResourceID WHERE (SF.FileName LIKE '%mp3') AND (SF.FilePath LIKE '%My Documents%') OR (SF.FileName LIKE '%mp3') AND (SF.FilePath LIKE '%Desktop%') ORDER BY SYS.Netbios_Name0