PowerQueryTool

Richard Bixler

Summary: In previous posts I've mentioned that I'll make available a tool that implements the quantitative methodologies I've described. This tool makes practical continuous quantitative planning, and execution to meet objectives, enabling agile-mindset management of complex projects.

So here are the links.

Published originally on svprojectmanagement.

You need to download and install two components:

• Download Files, and descriptive text. Included ReadMe files contain important installation information.
• The tool is free, licensed under terms of Free Software Foundation Gnu General Public License (GPL).
• This version of the tool is called PowerQueryTool, and provides a small template Program Management scenario, with focus on use of PowerQuery and its M Function Language, that are included within Microsoft Excel, as the database environment.
• There is much to say about PowerQueryTool. In the interest of keeping this Blog post short, an abbreviated description follows. For details on the tool, please visit PowerQueryTool at the PowerOpI website.
• PowerOpI website is at www.softtoyssoftware.com and provides tons of backup material and links. Capabilities of the PowerOpI Framework are described in detail at vbaConnection . Tips and troubleshooting can be found at vbaConnection.htm#troubleshooting.

The files provided include a small demo project built on the tool elements, illustrating table structures for project material allocation, product structure BOM, project dates, and product element data. The Framework methodology isn't tied to any particular project; it's a framework for simplifying and building tables that can be JOINed to represent many expansions and views of an overall effort.

My own efforts have applied this to managing projects for electronic hardware and software, including chip design: semiconductor wafer design, validation, fab, verification and Qual; telecommunications and audio/video compression and streaming hardware and software design, qual and productization; and enterprise class storage hardware and software development, prototyping, system development, and qual. The Framework has evolved in ways to handle these project sizes and complexities; and handled each without encountering limits to size or complexity.

For reference in descriptions below, Framework elements can be used for several configurations. These can be illustrated as Standalone, Data Source, and Client configurations. Standalone contains Data Source, QUERY, and Report elements. These can be split many ways to provide Data Source and Client instances, to support distributed use with centralized maintenance and security; particularly with SQL Server and MS Access.

ToolSource.xlsm (Reference: ToolSource) This is core functionality of PowerQueryTool. This file is set up as a Standalone configuration, and can also be used as a Data Source for ToolClient.xlsm. Walking through what's in this file:

• Reports (Green tabs): These are reports driven by QUERYs that JOIN Source Data Tables in the Red tabs. The Reports have been named for the QUERYs that drive them: qPQMProto (project overview), qPQMBuildDates (build dates for Ops), qPQMBuildSpend (simple budget for Finance, and for Ops orders), and qPQPurchReq (for tracking material purchase). Besides data contained, these illustrate use of JOINs by PowerQuery M. qPQExcelUnion illustrates UNION aggregation of several Source Data Tables using PowerQuery M. Most of the work needed in projects I worked on was handled by a handful of Tables, QUERYs and Reports just like these. (Reference: Reports)
• Source Data Tables (Red tabs): The demo project is represented in several tables, each of which handles a separate aspect of the effort, simplifying representation of the project as a whole. Each illustrates data for its problem representation, and Key structure and construction used for JOINing. Some include local "subqueries" done in Excel. (Reference: Source Data Tables)
  • Allocation equipment to be built and used by organizations shown with quantity, for testing identified, and configured as listed (Reference: Allocation);
  • BOM planning-bill of materials - product build tree structure representing product elements to be built and components needing special management for cost or effort, and configuration of each element, assembly, and revision (Reference: BOM);
  • Dates critical dates with associated keys, for all built and bought elements. These are derived from the table on blue tab qMPP_RollupExcel, imported from an MS Project export-file representing the project GANTT (Reference: Dates);
  • Elements base detail for each product element such as part numbers, supplier, distributor, configuration details (Reference: Elements);
  • CostAdjust and AdjustFactor control calculation of cost progression of prototype elements as prototyping and productization flow through build Phases (Reference: Cost Adjust);
  • PR tracking purchase lifecycle, material marshaling and distribution status (Reference: Purchase Request);
  • Group1, Group2, and Group3 Tables are used in the demonstration of the UNION QUERY (Reference: Union). JOIN and UNION are key functionality for this type of problem.
• Schedule Import (Blue tab): qMPP_RollupExcel is a Table from importing the file exported from MS Project representing the program GANTT schedule (Reference: Schedule Import).
• DataSources (Blue tab): the DataSources tab controls QUERYs and JOINs among local and remote Tables (Reference: Data Source Control). The DataSources tab contains several elements, ordered as follows in the Excel window:
  • tblConnectionConfig (Reference: Connection Config) Controls creation and configuration of connections to Tables in local and external files and databases. In the PowerQueryTool distribution, this controls configuration of Connections used by PowerQuery M QUERYs, defined in tblConnection.
  • Buttons (Reference: Buttons) User control of creation and update of Connections.
  • tblSQL(Reference: SQL Editor) Contains SQL-language QUERYs. PowerQueryTool focuses on M with PowerQuery, so this table is unlikely to be used in PowerQueryTool, although in other configurations SQL can be embedded within an M Query.
  • tblConnection (Reference: M and Connection Strings) Contains M Function-Language QUERY definitions, containing JOIN and COMBINE (UNION) QUERYs used with PowerQuery. (Some references at mstrings and connectionPQeditor).
• Variables (Blue) tab): Table of configuration variables to be used (optionally) when constructing QUERYs. (Reference: Data Source Control) The variables enumerated on this Tab are informed of file location et al., can be re-used among QUERYs to leverage relocation of files, re-naming of directories etc. in distribution and maintenance operation.
  • tblLocalizationConfig (Reference: Localization) Contains calculated localization variables for use in QUERYs.

ToolClient.xlsm (Reference: ToolClient.xlsm) This file is just like ToolSource.xlsm, but with all Source Data Tables in external ToolSource.xlsm file(s), with QUERYs in ToolClient.xlsm configured to access them.

P0001_Rollup.mpp (Reference: P0001_Rollup.mpp) This is a normal MS Project plan for the Demo project, with added VBA to provide Export capability to an xls file named after it, and an associated external Resource file. Several sub-projects (M0004.mpp, M0005.mpp) are rolled up into P0001_Rollup.mpp, to interconnect dependencies and deliverables among the sub-projects. The exported file P0001_Rollup.xlsx includes the connected sub-projects.

P0001_linked_tables.vsdm (Reference: P0001 VSDM) This is a normal MS Visio image, with added VBA to size and position graphical objects to a Timeline object on the Image illustrating flow of the Demo project.

All a mouthful, I know. Install the tool files, configure them, poke around. Change some data, Refresh DATA via button and see how Reports update to show your change. Add a product Element to the project. Add a Table, add it to a JOIN or create a new JOIN, and drive a Pivot Report with that JOIN. Then try a small project, cloning from the objects in the distributed files. Check out schedule updates rippling through, and Visio Images updating to reflect schedule changes. Increase project size and complexity as you become familiar with the methodology.

Once the tool is set up and your project and product structure are represented in Source Data Tables and JOINed, concentration shifts to managing the project using and updating Table data; and the tool fades into the background: Project and Product data updates frequently, and structure updates occasionally.

Setting up a second project is much more straightforward: your first project is the working path using PowerQueryTool in your file structure, organization structure, and product structure, that you can modify and extend; and also provides a record of Actuals available for estimates going forward. As you develop this infrastructure, and especially as you distribute the expertise, each project gets better than the last..

It's much more complicated to read and write about these actions, than it is to do them. Remember the PowerOpI website and support links above.