I wanted to create a practical example to hide and show columns with Power Query and user selections from a table 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 strike 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.
This worked great, except the columns loaded to the above table need formatting. So why not use some good ‘old school’ VBA to do this. I set this up so all that was needed was for you to select a cell in a Table and it looks for the word “Date” or a “$” in a column header name to apply date or currency with 2 decimal places formatting respectively. Yes obviously I will have to consciously ensure my Table headings have a :”$” character somewhere where it’s a currency column and “Date” if it’s a date column for this to work.
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.
[vb]
Sub TableQuickFormat()
‘————————————————————————————————————————
‘ 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
Exit Sub
End If
r.Select
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”
‘Case Else
End Select
Next h
Set r = Nothing
Set LO = Nothing
End Sub
[/vb]