Single Visualization from Separate Tables

[Anonymous]

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?

[mahoneypat]

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

[Anonymous]

That makes sense to simplify the input to the visual...If you're (@mahoneypat) able, I have a follow up question. I looked at the Microsoft User Docs for Appending (https://docs.microsoft.com/en-us/power-query/append-queries). It does not appear that I have the menu available that is shown in the document in order to append. Are there troubleshooting steps to add this? 

[Ashish_Mathur · Accepted solution]

Hi,

The video at this link should help - Power Query #08: Import Multiple Excel Sheets From Multiple Excel Workbooks - YouTube.