I wanted to see if I could get Excel’s Power Query (Get & Transform on the ribbon of course) to drive the showing or hiding of columns in a report. In the past I would do this with VBA effectively enough (I will do a post in the future on the purely VBA method I have used) but hey the Power Query bug spares no one and so I love using it in place of VBA I have to admit.
This is the my first Power Query post so I hope I can stike the right balance between brevity and clarity. So I did the above with 3 fairly simple queries:
- GetTxData which was the simplest of queries – it simply loaded the full data report (in a Table on another sheet) with all columns, no other steps as Connection only.
- ShowHideCols which took the column names from the above orange table in cols A & B (which are exactly the same columns as in the full data report used in above), filtered by “Y” for showing only those columns and transpose them – a connection only query.
- ShowSelColsOnly which Appends the above 2 queries including the one row “Y” indicator, transposes, filters out the rows not equal to Y and then transposes back to the format needed and loaded to the above location.
Worked great except the columns loaded to the above table need formatting so some good old VBA was needed after all and so long as you select a cell in a Table it looks for the word “Date” or a “$” in a column header name to apply date or currency with 2 decimal places formatting respectively. Actually I was glad to write that little Excel automation macro, as I often have to format tables columns usually for date, currency, percentages and whole numbers which can be a bit tedious. So once I have enhanced it for a few more formats it will go into my MyMacros Add-In where I have a collection of macros I use for day to day work see below.
Below is the VBA code I used in this example which provided you have selected any cell in a table, looks for a “Date” or “$” in the header row. You can see how the Case statement could easily be expanded for other formats like percentage or whole number and others.
‘ Procedure : TableQuickFormat
‘ Author : John Hackwood theexcelfactor.com e:[email protected]
‘ Date : 28/12/21
‘ Purpose : User selects a Table and any columns with Date are date formatted or with $ in col name then dollar formatted
Dim LO As ListObject
Dim Tablename As String
Dim r As Range
Dim h As Range
‘User must select a cell within the Table to be formatted
On Error Resume Next
Set r = Application.InputBox(Prompt:=”Select a cell within a Table to be formatted or cancel”, Title:=”Quick Table Format”, Type:=8)
Tablename = ActiveCell.ListObject.Name
On Error GoTo 0
‘handle if user cancels or doesn’t select a Table
If r Is Nothing Or Tablename = “” Then
MsgBox “You have chosen to CANCEL or not chosen a Table, so this formatting process will end”, vbExclamation, “Quick Table Format”
Set r = Nothing
Set LO = ActiveCell.ListObject
For Each h In LO.HeaderRowRange
Select Case True
Case (InStr(h, “Date”) > 0)
LO.ListColumns(h.Value2).DataBodyRange.NumberFormat = “m/d/yyyy”
Case (InStr(h, “$”) > 0)
LO.ListColumns(h.Value2).DataBodyRange.NumberFormat = “$#,##0.00”
Set r = Nothing
Set LO = Nothing