In this post we’ll look at how to sum-up and report on unpaid Invoice amounts with a currency data type in Power BI using the Merge Queries feature. We’ll see how to manage related tables that have their relationship defined by a SharePoint lookup column by building these merged queries and specifying the columns we want to expose. We’ll facilitate all of this with a SharePoint lookup column as the index/foreign key across lists in an Office 365 SharePoint tenant, and please note, all of this will work/behave the same in an on-premises SharePoint farm as well.
The Invoice and Client Relationship – AngularJS App Demo
In this example, we have an Invoices List and a Clients List. A Client can have many Invoices, and an Invoice can have exactly one Client. Let’s take a look at adding invoices in a custom AngularJS App I’ve built for managing small-to-medium business operations.
So, you can imagine, there are multiple clients, and they have many of these unpaid invoices. Each of the invoices, items, totals, etc. are stored in SharePoint and ready for us to report against in Power BI.
Using Merge Queries in Power Query Editor
If you’re unsure how to import SharePoint list data into Power BI Desktop – have a look at this post. I’ll be starting from within Power Query Editor after having imported my list data.
As mentioned, a Client can have many Invoices, and an Invoice has exactly one client, and this is facilitated through a SharePoint lookup column. The lookup column is named “BMOAssociatedClient”, and when you import the Invoices list into Power BI, you only get the ID field of that Associated Client. This is the column “BMOAssociatedClientId” you’ll see in the next video.
However, when we produce our report, we need the Client Name, and the only way to get that is to create a relationship (i.e. JOIN) between the Invoices table and the Clients table. That is done using Merge Queries as follows:
I also slipped in a Change Type action if you didn’t notice. I’ve converted the Invoice Total amount to a decimal so that it can later be converted to currency for our final report in Power BI.
Expand and Aggregate in Power Query Editor
Another requirement is to pull in the exact field we need to surface in our reports, that is the Client Name. That will be the legend value for our report charts and is stored in the Clients table under “BMOClientName”. This is a straightforward action in the Power Query Editor using the Expand and Aggregate button above our newly added Table column:
Note at the end that I removed the relationship on the FileSystemObjectType column. This was done because there is no actual relationship, and if that link exists and you try to refresh data, Power BI Desktop will error out.
Build the Power BI Invoice Report
Now on to the fun part, building the Power BI Invoicing Report. Of course, use your own judgement, requirements, imagination, etc. to build what works best for you or your clients.
Here, I’m using a simple pie chart which is still quite effective at showing me what I’m after: who has pending payments on my ledger, and how much exactly is in the queue for my credits. Please take note that I use the Power BI Desktop Modeling > Format function to quickly convert the total decimal value to a Currency data type/format:
Categories: Business, Business Management, Office 365 and O365, Power BI, SharePoint
Leave a Reply