Power Query: Self-referential Scripts

This article will demonstrate the value of a self-referential Power Query script in an Excel timesheet forecast workflow.

What is a self-referential  Power Query script?  It is a script whose input and output are the same table.  This is useful in scenarios where user input must be maintained while a table is transformed.

Requirements

Management attends a Monday morning meeting to allocate staff hours to various projects for the coming week. A simple workflow must be designed that allows the user to input staff & project information, and hours against each.

Execution

The below steps detail a workflow which allows the user to enter an employee record (row) into a Staff Table and then have the employee name appear as a field (column) on a Project Table.  At this point, hours can be distributed accordingly.

The results are demonstrated in the gif below – columns in the green Power Query table will expand, contract and sort themselves according to data entered into the blue table:

Recording2

Step 1) Create a Self-referential Query

  1. Load the below Project Table into Power Query.

    2016-01-06 12_26_48-Timesheet Forecast.xlsx - Excel
    Note: the hours under Jane and Bob are placeholders.
  2. Name the query “tbl_Project” and immediately ‘Close & Load’ it to a table in a worksheet
  3. Re-open the newly made query and replace “Table1” with “tbl_Project”:
    let
     Source = Excel.CurrentWorkbook(){[Name="tbl_Project"]}[Content]
    in
     Source
  4. ‘Close & Load’ and then delete the old table, Table1

Step 2) Create the staff table

  1. Load the below Staff Table into Power Query:2016-01-06 12_19_58-Timesheet Forecast.xlsx- Excel
  2. Filter out nulls from [Employee]
  3. Add an Index starting at 1
    let
     Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
     #"Filtered Rows" = Table.SelectRows(Source, each ([Employee] <> null)),
     #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1)
    in
     #"Added Index"
  4. Name the query “tbl_Staff” and ‘Close & Load’ to ‘Only Create Connection’

Step 3) Mashup!

Re-open the tbl_Project script and paste in the below:

let
 Source = Excel.CurrentWorkbook(){[Name="tbl_Project"]}[Content],
 #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1), // Maintains sort order during merges
 #"Replaced Value" = Table.ReplaceValue(#"Added Index",null,0,Replacer.ReplaceValue,Table.ColumnNames(#"Added Index")), // Prevents staff with null hours from being automatically filtered out during unpivot
 #"Unpivoted Employee Hours" = Table.UnpivotOtherColumns(#"Replaced Value", {"Status", "Project Type", "Client", "Index"}, "Employee Name Old", "Hours"),
 #"Merged Queries" = Table.NestedJoin(#"Unpivoted Employee Hours",{"Employee Name Old"},tbl_Staff,{"Employee"},"NewColumn",JoinKind.RightOuter),
 #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Employee", "Index"}, {"Employee", "Index.1"}),
 #"Sorted Staff Names" = Table.Sort(#"Expanded NewColumn",{{"Index.1", Order.Ascending}}),
 #"Removed Columns" = Table.RemoveColumns(#"Sorted Staff Names",{"Employee Name Old", "Index.1"}), // Cleanup must be here so the Pivot function returns the desired results
 #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Employee]), "Employee", "Hours", List.Sum),
 #"Sorted Client Names" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}),
 #"Removed Columns1" = Table.RemoveColumns(#"Sorted Client Names",{"Index"}),
 #"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each ([Client] <> null))
in
 #"Filtered Rows"

In a nutshell, the above script un-pivots Project Table [Employee Names], right outer joins it to the Staff Table and finally re-pivots. This effectively expands or contracts the Project Table while maintaining any hours currently inputted.

Step 4) A Dash of VBA

Adding the below VBA script to the worksheet containing the tbl_Project table automates the Power Query refresh:

Option Explicit

Private Sub Worksheet_Activate()
 Application.EnableEvents = False 
 On Error GoTo errHandler 
 Sheet2.ListObjects("tbl_Project").QueryTable.Refresh BackgroundQuery:=False ' Change Sheet2 to the sheet that contains tbl_Project

softExit:
 On Error Resume Next
 Application.EnableEvents = True
 Exit Sub

errHandler:
 '...
 MsgBox Err.descrption
 Resume softExit
 
End Sub

Final workbook: Timesheet Forecast

Final Thoughts

The workflow in this article is very powerful.  It is, for all intents and purposes, a simple relational table workflow which lets the user to define the staff dimensions and enter their associated hours into a denormalized project fact table.

I am not aware of any other way to accomplish this inside Excel without an excessive use of *shudder* VBA.  I invite anyone to take this idea and run with it – it can lead to some very interesting solutions.

Simon Nuss

Power Query: Self-referential Scripts

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s