Power BI – International Expense Reports with Office 365 SharePoint

SharePoint and Power BI come together perfectly in Office 365 to create an expense report solution that easily accounts for your team’s global presence and globally accrued expenses. “Create an expense report” solution in SharePoint is an old scenario, but with Power BI coming to the forefront, I know you’re going to get more than the same-old-story out of this post. 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 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:

ExpenseListDefinition

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:

ExpenseSnippet

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 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:

ManageRelationships

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.

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 ReportBlue 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.

 

 

 

Advertisements


Categories: Apps, Business, Office 365 and O365, Power BI, SharePoint

Tags: , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: