Power Query - Compensation Plan

[Stoner7Matt]

I am trying to create a bonus compensation calculator for my employees. There are 5 different compensation plans. The Raw data is already a scheduled task which creates an excel file and saves to the same folder under the same name at a designated time. I would like to query this data for each type of compensation plan and create a template to give them an update on their progress (based on profitability contained in the data).

My approach is using microsoft query and setting filters based on username (in the data). I need this data to refresh daily so the will be able to see their progress. This data will be saved new each morning.

Is microsoft query the best way to approach this? I need refreshable data. I would have to create a ODBC file for each person. Any help or brainstorming would be greatly appreciated.

I want my employees to understand the P and L effect the have on the company. I believe live data will help everyone make better decisions.

Thanks!

 

[Stoner7Matt]

But can you schedule the refresh to happen automatically unattended (like when you're sleeping)? That's what I thought we were talking about here and that's the whole point of Power Update.

That may be the whole point of Power Update. However every single ERP or Accounting system i've ever used has the ability to schedule reports to run in the back ground. If if their web-based it can add some difficulty. The refresh happens right when I open the spreadsheet. Even if it didn't I just click 1 button and it refreshes. I think what you're implying is live data which could be useful. In this situation I only need anything <= previous day.

 

[TimRodman]

I see. You must be dealing with a small amount of data. I used to do the same thing with Microsoft Query and make it refresh when the file opens.

But now I have over 20 files that take a few hours to refresh. That's why I use Power Update to refresh them at night. That way users just need to open the Excel files and they know that the data is current from the previous night.

 

[Stoner7Matt]

It's about 60 employees. Each has a spreadsheet saved on their desktop and it queries about 5 reports. I'm wondering if I'm going to run into your issue and have to goto power update at some point. As of now it works great but I see what your getting at...

 

[miguel.escobar]

MS Query is cool! but I rarely use it. I prefer the more user-friendly way that Power Query uses and how active their development team is to the point where if something needs an optimization or similar, you can expect such feature or optimization within the next few days or months. (that's WICKED cool)

Going back to your scenario, I'd probably use the following:
Creating a VLOOKUP Function in Power QueryThe Ken Puls (Excelguru) Blog
Building a Parameter Table for Power QueryThe Ken Puls (Excelguru) Blog
and perhaps these 2
Append or Combine Data in Excel - Power Query Training (if you can have all the files in a folder, or you might want to create a parameter table if you need to use multiple connections)
Grouping or Summarzing Data in Excel - Power Query Training (if you wish to group or summarize data so you can calculate the compensation at another level)

that's all the brainstorming that I could do with the information you provided.

I help this helps.

 

[TimRodman]

I agree with you Miguel, especially now that Power Query is part of Excel in Excel 2016. No more need to install the add-in on everyone's desktops.