Using Power BI & SharePoint Online to Solve the QuickBooks Online “Type” Void

The post QuickBooks Online & SharePoint Online Synchronization Revisited provides an overview of how Power BI can be used to not only synchronize both online products but also integrate them. The post SharePoint for Customer Management discusses how a SharePoint Online list can be used for customer management. One of the fields in the SharePoint Online Customer list is “Customer Type”. While the QuickBooks desktop version supports various “types” for financial and management reporting, the QuickBooks Online version (as of the time of this post) does not support these capabilities. Below is an illustration on how Power BI, SharePoint Online, and QuickBooks Online can be brought together to full this reporting void. The illustratiom is a Power BI report displaying total customer billings by customer type.

The first consideration is how we are going to sync the SharePoint Online Customer List with QuickBooks Online customers. The post QuickBooks Online & SharePoint Synchronization discusses how customers can be imported and exported using Excel. I’m going to assume that SharePoint Online is being used as either the CRM or lead tracking application. Consequently, the customer data in SharePoint Online is imported into QuickBooks Online via Excel.

Here are the overall steps I undertook to develop the report in Power BI Desktop.

1. Because I already established a connection to my SharePoint Online site, I chose Recent Sources and then the SharePoint connection.

2. Selected the Customer & Prospect List table and then edited the query to only include Customer Name and CompanyType. I also renamed the columns Customer and Customer Type.

           

3. I connected to the QuickBooks Online data source, you can see it in 1, and selected the Customer table. I eliminated all the columns expect for CustomerParent, Customer Full Name, and Id. Renamed Id to CustomerId and CustomerParent to Customer Name. The reason I needed CustomerParent is because QuickBooks Online treats jobs/projects as Sub Customers. The total billings report that I am illustrating is at the Customer level so we need to know the parent name.

4. I then selected the QuickBooks Invoice table and only kept the columns you see below.

5. I then set the relationships between the quires.

6. Just to be on the safe side, I copied the Customer Name from both the SharePoint Online Customer & Prospect List and QuickBooks Online Customer quires and pasted them into Excel so I could compare them via a formula to make sure the names are exactly the same.

7. Lastly I added the three tables you see below to form the report.

For illustration purposes, I kept the report simple. Parameters could have been added for dates, amounts >, etc. Graphs and drill downs could have been used for visual effects. My purpose was to show how Power BI and SharePoint Online can be used to improve financial and management reporting for QuickBooks Online users.

Note: Organizations not using SharePoint Online can use a simple Excel Worksheet with client name and customer type to develop the same report.  Power BI Desktop comes with a connection for Excel data sources.

Leave a Reply