In this post, Power BI Expense Reports will be created Office 365 using SharePoint. You’ll see how to create an expense report solution that easily accounts for your team’s global presence and globally accrued expenses. Below I demonstrate how to use these technologies, along with trustworthy web site data from Wells Fargo (as an example), to quickly build an expense report solution that converts global/worldwide expenses to a single currency…US Dollars in this case.
Power BI Expense Reports – Exchange Rates
The first step to creating the solution is to import currency exchange rates from a reputable data source. I chose Wells Fargo for this demo solution, but please note that in a production environment you should thoroughly understand the terms of service (TOS) and licensing for any third-party services you leverage.
- Launch Power BI and use the Get Data function to import data from a Web Site
- Specify the URL and choose the Edit option to launch the Power Query Editor
- Apply required transformations to prepare imported data for the final solution
First, I am going to convert the Country column to a more standardized human-readable format so it matches the ‘Country’ field options in SharePoint (covered later). I’ll do this using the Power Query Editor function Transform > Format > Capitalize Each Word feature.
Next, I will down-size the Conversion Rate provided by Wells Fargo down to only the numerical conversion rate value. This will be done with the Power Query Editor functions:
- Text After Delimiter
- Text Before Delimiter
Finally, I will convert text to a decimal value in Power BI using the Change Type > Decimal Number option.
This is all done as a preparatory step to executing our currency conversions.
Create a SharePoint List
In a production scenario, I’d create this list using site columns, content types, and a well thought through set of SharePoint list views. I’ve written several posts on these subjects, so please, feel free to browse through my blog. For this post, let’s just dive right in. We’re going to create a simple list consisting of:
- Title – the description of the expense
- Employee – the employee who accrued the expense
- Country – country where the expense transaction occurred
- Amount – the amount of the expense in the local country currency
Note that I used simple copy-and-paste to move the Country values from Power BI into the SharePoint “Country” column. It is important that these values match exactly as “Country” will be the foreign key that relates our SharePoint Expenses List entries to the exchange rates table. So, I do recommend using copy-and-paste. Here’s the definition for the list:
And…a few entries for the demo. Side note on this: SharePoint list items have an “Attachments” field out-of-the-box. You could (should?) scan receipts and attach them to these items:
Import SharePoint List into Power BI
Import the SharePoint List into Power BI using Get Data > Online Services > SharePoint Online List. The import process is straightforward, but after importing, there’s a critical step which is to Expand the SharePoint Person or Group field to its First Name and Last Name parts. Those human readable values are what we need to show in our final Power BI Expense Report:
Power BI Expense Reports – Models – Manage Relationships
It was quite intentional that I named the “Country” column in SharePoint exactly that (i.e. “Country”). The reason for doing so is because Power BI will automatically build the relationship between SharePoint and my external provider tables because the columns are named the same. If you diverged from the example, here’s a quick pointer:
- In Power BI Desktop – navigate to the Model interface.
- Click Home > Manage Relationships
There, you will be presented with the necessary UI for setting the relationship between the Country field in SharePoint and the Country field provided by your exchange rate web service provider; that is, if the names differ and you didn’t realize the benefit of auto-relationships.
Set whatever you named “Country” in SharePoint to whatever the name of “Country” is/was in your third party service:
Power BI – Related Function
This is where the rubber meets the road. Let’s see how to use the Power BI Related Function from the [M]ashup Language for creating a custom column in Power BI which will hold the calculation of our original country’s expense converted to U.S. Dollars (USD).
Almost there! This is the time to validate your data.
750 Australian Dollars at a rate of .7471 AUD to 1.0 USD = 560.325 – correct.
55 Chinese Yuan at a rate of .1591216 CNY to 1.0 USD = 8.751688 – correct.
And, so on….the conversions are correct through the use of Power BI foreign keys and the resulting Power BI Related function.
Final Power BI Expense Report
The heavy lifting is done, and you can now build your report front-end however you want and publish it to Office 365.
I’ve already written numerous blog posts on how to create reports and publish them to Office 365, so again, please take a look at the Power BI section of my blog for reference.
For this post, let’s just take a look at some of of the more immediately pertinent details behind our expense report.
- First, I’m using the Microsoft Excel Expense Report – Blue Theme to insert a graphic. I’ve done this with the Power BI Desktop > Insert > Image feature.
- Next, I use the Microsoft Power BI > Table report option for the main report data UI.
- Finally, I use Power BI Slicers to make useful filter controls available.
Here’s a demo of the final interface in Office 365.
This could also be updated to include Expenses by Country, Expenses by Employee, Expenses by Month, and so on…
Thanks for reading.
Categories: Apps, Business, Office 365 and O365, Power BI, SharePoint
Leave a Reply