Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Single Visualization from Separate Tables

Hello All, 

 

We are working on creating a T&E Expenses Dashboard as an audit group where we can view/filter the expenses in a variety of ways, then drill down to the raw data to export to Excel. We have 4 main data groups that are in separate tables: Airfare, Uber, Lyft, and Everything Else. Those are tied to a table of just employee numbers (duplicates removed). 

 

I was asked to start with expenses by department. What has been created so far are 4 separate pages with visualizations for each group. The main visualization is a matrix with department on the left and columns for each quarter (this will be year when more data is uploaded). Then under each department in "Everything Else" is a breakdown by expense category (meal, hotel, etc.). 

 

So far, the feedback has been good except for one issue, we want one page with all of the information. The simple solution would be to include Airfare, Uber, and Lyft as expense categories. From the information provided, is there any way to accomplish this?

Need Help

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    If possible, the best thing to do would be to append your four tables together.  Rename the columns in all 4 queries to make them the same, so when you append they all go into the same columns.  Hopefully you have similar columns in each (Date, Amount, EmployeeID, etc.).  You can add a custom column to each before the append with a constant text value to indicate the source (Lyft, etc.).  Once appended, you can uncheck Enable Load on the 4 source queries.  Your analysis and visualization will be much simpler if you can do that.

    Regards,

    Pat