VBA/ Power Query project - blueprint or strategy needed

Hi all,

I am starting a new project at our firm and I need some basic guidance on how to achieve what I'm going for. I can probably work out the details or come back with specific questions, but right now I want a general blueprint or approach.

Basically, we have a new (and better) time system, so a macro I made earlier that takes excel generated time reports and turns them into a clean pivot table for presentation- that I spent a good amount of time on- is now kind of useless, but the process was very educational and I can do even better work. In a way I should be able to do what I did before and it should be even easier as the reports do not have breaks in the rows of data but is more properly formed as as table.

There are two things that throw me off right now and take me out of my element:

First, I was wondering if Power Query could be used. For some reason, since I learned about PQ, I want to find a place to use it and this seems like it could be a good place, but maybe it isn't. We will need to export a table of data from our system to excel, strip away some of the columns, and we will need to make a pivot table of that with the staff names as the columns, the service descriptions as the rows, and the sum of the hours per service description as the values. What would indicate the use of Power Query as opposed to VBA if we would have to use this on multiple newly generated workbooks? I suspect that last fact may invalidate it's use. I think Power Query M language is like the xlsm workbooks. They aren't part of your Excel system, right? They are bundled only with individual workbooks otherwise they're impracticable.

Second the new reports only have the time codes (ints), not the descriptions (strings) of what those codes represent, which we need to show up on the table, so I'd have to translate them, maybe with a lookup table or something.

I extracted a pdf mapping of all of this into a csv file so maybe that can be used for lookup in a macro or something. If strictly VBA and not PQ, would this data be stored as an array in my code or would it be stored as a separate file in the C Drive that is read from VBA? That would require more learning, but that's what I love.

For example, the Svc Code "15000" represents Description "ADM Administration" and there are other columns that go with it as well but which aren't as important for these purposes.

The Svc Code A2300 represents "AUD Acounts Receivable" - i.e. audit procedures performed on testing accounts receivable. Firm wide we have a few hundred codes, for tax, audit, marketing, admin, business services, etc.


Can M from Power Query be embedded in VBA? Would that be a good approach?

Otherwise I can probably make it work.

Thanks for general guidance to an abstract question.

Originally Posted by alansidman

I am not aware that you can embed Mcode into VBA. However, if you post a sample worksheet (sanitized for confidential material) and show a before and after scenario (mocked up), we will attempt to give you a viable solution.

I thought you could maybe store the M code as a string and pass that to a PowerQuery call, but I think I'm on the wrong track using power query here.

My main challenge probably is transforming a service code into a description based on a "lookup table" type operation (not sure if there's a more specific name for that).

Ok so in the attached workbook:

- Tab 1 is the raw data that the system generates (along with a pivot table I just made).
- Tab 2 is the csv file that I created to translate codes into descriptions- and also join other data like department if necessary. Note that this will not ever be in the excel workbook or even open as a file. The catch is that that it will be stored as a static file on the C drive of the user, or else maybe incorporated into a (very large) static array or something.
-Tab 3 is the old pivot table data table, an intermediate step from a messy raw output.
-Tab 4 is what I want the end product to look like, or something like that. I can deal with a lot of the nuts and bolts.

I guess I need help with the translation of service codes to descriptions from a file on the PC and potentially importing other data based on that. I haven't started coding but I will when I have time. I can troubleshoot and handle little issues. I have done a program (the last program) that writes to a file called "savesettings.txt" in a given drive and if it doesn't exist, creates it. That stores the location as a string on one line where the user wants their workbooks to be saved, as a way to achieve persistence of a variable. I have not worked with multi line files or comma separated, but I'm excited to have a chance to learn this if that's what needs to be done.

Maybe this step is not that hard and I could find on google but I thought I'd ask and I was also hung up on the idea of Power Query. If there is some way to use that or power pivot or something (which I have little/no experience with) to save time or be more elegant, I'm still interested. In the new reports, we don't see the total hours per employee by service line until we do the pivot, whereas in the other, that was precalculated but that should be no problem to produce. Also in cell W46 for instance, that value represents dollars, wheresa most of the surroundings represent hours, but I think I can fix that easy in the table before running the pivot operation.

Mainly yeah should I avoid Power Pivot Power Query and best way to translate codes into descriptions or associated values in a kind of 'dictionary' or table.

Cool thanks! I figured out the "Table Lookup" function (my choice of words) below. That was the big thing I needed I guess.

This is not the complete production code. It's probably not optimized either. I'm not sure it is optimal to open the csv lookup file for each entry that needs looking up. What is best practice for looking up multiple values in a table/separate file in terms of optimization? Should I make a 2D array at the outset? Or maybe it's not that bad.

Feels good to be improving, growing and becoming useful.

Please Login or Register  to view this content.