In this brief article, I’ll demonstrate how to identify missing values from your Connectorly views.
Previously, I introduced the Connectorly Views, which help you create flexible reports on your Xero data to explore different cases and scenarios regarding your finances.
Connectorly Views currently works with the Chart of Accounts, the Contacts, and the Products. In this article, we use the Chart of Accounts as an example. Let’s say you have already created a couple of views and now wish to quickly find out what Chart of Accounts is not part of your views.
On the Connectorly Portal, you see the already assigned elements in grey and the available elements in black.
This view is complex to manage when you have many 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 in our views.
First, download one of the Connectorly Xero templates and open it. Connect to your database, and let’s start it.
Add a new empty page where we can check the result of our activity.
Now, we will create two new tables that will help us 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
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, which means they are not assigned to any views.
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])))
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.
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 will assign a 200RL account code to one of my views.
Go back to the Power BI desktop and refresh your report. You will see that the 200RL is no longer in the “Not used Chart of Accounts” list.
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 different ways to reach the same goal; this is just one solution.
Let us know if you have an easier way to use it.
Try Connectorly for Xero & Power BI
TAKE YOUR FINANCIAL DATA ANALYSIS TO THE NEXT LEVEL
Free Trial
