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!

 

[scottsen]

So, Power Query is a great tool for importing and shaping your data. Typically, I then do some modelling in Power Pivot, then build reports in Excel.

The refresh story is a bit wonky. If you have SharePoint... refreshing on SharePoint is sometimes and option, but it doesn't work with Power Query. (boooo!).

Honestly, i think you are going to need some automation for the refresh, either writing your own (excel 2013 has a Model.Refresh method in VBA), or by usinng Power Update: Introducing Power Update! - PowerPivotPro PowerPivotPro

 

[TimRodman]

I've been using Power Update and it works great. You can have it send the refreshed results to a folder, SharePoint, Office365, OneDrive, or by email.

You can also have the email attach a PDF of the results instead of the Excel file which is nice in some cases.

 

[Stoner7Matt]

Thanks Tim! I currently don't have Power Update but am requesting this from our IT Provider (corp setting kept us from having this..) I sucessfully ended up using Microsoft Query but I know this is an older method. I'll post back when I begin using Power Update. Sounds like this is a much more efficient tool with better options like you're refering too.

As for scottsen, thank you for the reply. However rather than purchsing an add-on it appears excel already has the functionality to complete this task. Does everyone try to sell their stuff on here? I think that sort of defeats the purpose.

 

[scottsen]

Your reply confuses me -- Tim and I said (more or less) "Your best option is probably Power Update", which you are apparently trying to get. I certainly make no money off Power Update, doubt Tim does. It's just the easiest way to fill the refresh gap today. If you would like to avoid that... then like I said (if you are using 2013) you can try Model.Refresh as well.

I have never used "Microsoft Query" -- how is that helping your refresh story?

 

[TimRodman]

I don't make any money off it either. I'm just a happy customer. For two years I longed for an easy way to refresh stuff, but was confused by all the complicated VBA code that I found online and even more confused by how to get it to work in SharePoint. That's why I was so happy to find Power Update. I even wrote a post on Rob's blog about how happy I was.

Power Update is free to try on one Excel file. If you like it, they charge $500 to use it on as many Excel files as you like.

There are a lot of third party programs out there that make our jobs in Excel easier. I personally appreciate it when others share their experience about using them. It helps me to determine which ones are really worthwhile.

 

[Stoner7Matt]

Thanks for the input. I can do all this in Microsoft Query so no need to purchase anything..

 

[TimRodman]

Please share. I'm very interested. Does it involve VBA?

 

[Stoner7Matt]

It does not. Unless you guys are talking about a different refreshing capability all i'm doing is using the Microsoft Query Add-on. I need data from a system generated excel sheet (comes from our ERP) to push data into a spreadsheet that formats this data into several pivot table reports. I filter the data based on the dates I want to capture in MS Query. Each day I can login (our ERP delivers a new report at 2:00 AM each day to the same file location with same file name), I refresh the data connection and it adds new data entered into our the previous day ERP. Am I missing something you guys are getting at with Power Update?

Here's a link to the MS Office support tutorial. Youtube also has some good videos on this. If you need more of an explanation on this feel free to message me.

https://support.office.com/en-ca/ar...nal-data-42a2ea18-44d9-40b3-9c38-4c62f252da2e

 

[TimRodman]

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.