dashboard-user-panel-template

How can you identify values missing from a table in Microsoft Power BI Desktop?

In this brief article, I’ll demonstrate how to identify missing values from your Connectorly views.

Previously, I have introduced the Connectorly Views that help you to create flexible reports on your Xero data to explore different cases and scenarios on your finances.

Connectorly Views works with the Chart of Accounts, the Contacts and the Products at the moment. In this article, we use the Chart of Accounts as an example. Let’s say you have created already a couple of views and now you wish to easily find out what are those Chart of Accounts that are not part of any of your views.

On the Connectorly Portal, you see the already assigned elements in grey colour and the available elements in black. This view is a bit hard to manage when you have a huge amount of Chart of Accounts in your Xero company or companies.

Use the Connectorly Xero templates

As you are already set up and connected to your Xero data, let’s use Microsoft Power BI desktop to show which account codes are not used at all in our views.

First, download one of the Connectorly Xero templates and open it. Connect to your database and let’s start it.

Find_invoice_template How can you identify values missing from a table in Microsoft Power BI Desktop?

Add a new empty page where we can check the result of our activity.

Now, we are going to create 2 new tables that will help us to identify the non-used Chart of Accounts. In the Connectorly Xero Data Model, we hold all the Xero Chart of Accounts data in the “xero accounts” table.

Let's identify missing values from your Connectorly views

Table_view How can you identify values missing from a table in Microsoft Power BI Desktop?

Now let’s switch to the Table view

Under the Table tools click on New Table.

Now we will use a DAX expression to create two new tables. The first will hold all account codes (without duplication). The second one will only hold those Chart of Accounts that are not in the “xero account_views” table, means they are not assigned to any views.

New_table How can you identify values missing from a table in Microsoft Power BI Desktop?

Use the following DAX expression to create your first table:

ALLCODE = INTERSECT(CALCULATETABLE(DISTINCT('xero accounts'[Account Code])), CALCULATETABLE(DISTINCT('xero account_views'[Account Code])))
ALLCODE_table How can you identify values missing from a table in Microsoft Power BI Desktop?

Click on the New table button to create the second table with the following DAX expression:

ALLMISSINGCODE = EXCEPT(CALCULATETABLE(DISTINCT('xero accounts'[Account Code])), ALLCODE)

Now, go back to the Report view and add two Table visualizations to see all the charts of accounts and the missing account codes.

Power-BI-visualization How can you identify values missing from a table in Microsoft Power BI Desktop?

Test our solution

Let’s go back to the Connectorly portal and make some changes to include one of these missing account codes in a view.

In this example, I am going to assign 200RL account code to one of my views.

Assign_200RL_to_view How can you identify values missing from a table in Microsoft Power BI Desktop?
Resultmissingcodes How can you identify values missing from a table in Microsoft Power BI Desktop?

Go back to the Power BI desktop and refresh your report. You can see that the 200RL is not in the “Not used Chart of Accounts” list anymore.

Summary

This is a quick way to check if you included everything in your views. You can also use this trick if you need to find records that are present in one table but not in the other one. Of course, there are other ways to reach the same goal, this is just one solution.

Let us know if you have an easier way you use.

Try Connectorly for Xero & Power BI

TAKE YOUR FINANCIAL DATA ANALYSIS TO THE NEXT LEVEL

Comments are closed.