In this blog post, I will show you how to replace space from the Power Query step name using VBA. This can be useful if you want to avoid errors or inconsistencies when referring to your query steps in your code.
Power Query is a powerful tool for data transformation and analysis in Excel. You can create queries from various sources, such as tables, files, web pages, etc., and apply different transformations to them. Each transformation is recorded as a step in the query editor.
Most of the time UI generate a step name with space in between them. And we have to refer to them by #”Step Name” which is not convenient. You can change the step name manually. However, this can be tedious and error-prone if you have many steps with spaces in their names. A better solution is to use a VBA macro that can automatically replace all spaces from all your query step names with a specified character.
Option Explicit
Private Const MULTI_WORD_STEP_NAME_PREFIX As String = "#"""
Private Const MULTI_WORD_STEP_NAME_SUFFIX As String = """ ="
'Helper enum
Public Enum Comparer
IGNORE_CASE = 0
CONSIDER_CASE = 1
End Enum
Public Enum RelativePosition
FROM_START = 1
FROM_END = -1
FROM_BOTH = 2
End Enum
Public Sub RemoveSpaceFromStepName(FromWorkbook As Workbook)
If FromWorkbook Is Nothing Then Exit Sub
Dim CurrentQuery As WorkbookQuery
For Each CurrentQuery In FromWorkbook.Queries
Debug.Print " >> Removing Space From Query : " & CurrentQuery.Name
CurrentQuery.Formula = GetQueryFormulaAfterSpaceRemoved(CurrentQuery.Formula)
Debug.Print
Next CurrentQuery
End Sub
Public Function GetQueryFormulaAfterSpaceRemoved(QueryFormula As String) As String
Dim CodeLines As Variant
CodeLines = Split(QueryFormula, vbNewLine)
Dim AllStepNameWithSpace As Object
Set AllStepNameWithSpace = CreateObject("Scripting.Dictionary")
Dim CurrentCodeLine As Variant
For Each CurrentCodeLine In CodeLines
CurrentCodeLine = LTrim(CurrentCodeLine)
If IsStartsWith(CStr(CurrentCodeLine), MULTI_WORD_STEP_NAME_PREFIX) _
And Contains(CStr(CurrentCodeLine), MULTI_WORD_STEP_NAME_SUFFIX) Then
Dim StepName As String
StepName = BetweenDelimiter(CStr(CurrentCodeLine), MULTI_WORD_STEP_NAME_PREFIX, MULTI_WORD_STEP_NAME_SUFFIX)
AllStepNameWithSpace.Add MULTI_WORD_STEP_NAME_PREFIX & StepName & """", GetStepNameWithoutSpace(StepName)
End If
Next CurrentCodeLine
Dim FinalQueryFormula As String
FinalQueryFormula = QueryFormula
Dim CurrentStepName As Variant
For Each CurrentStepName In AllStepNameWithSpace.Keys
FinalQueryFormula = VBA.Replace(FinalQueryFormula, CurrentStepName, AllStepNameWithSpace.Item(CurrentStepName))
Next CurrentStepName
GetQueryFormulaAfterSpaceRemoved = FinalQueryFormula
End Function
Private Function GetStepNameWithoutSpace(StepNameWithSpace As String)
GetStepNameWithoutSpace = VBA.Replace(StrConv(StepNameWithSpace, vbProperCase), Space(1), vbNullString)
End Function
'Helper function
Public Function IsStartsWith(OperationOnText As String, TextToMatch As String, Optional ComparisionType As Comparer = IGNORE_CASE) As Boolean
If ComparisionType = CONSIDER_CASE Then
IsStartsWith = (Left$(OperationOnText, Len(TextToMatch)) = TextToMatch)
ElseIf ComparisionType = IGNORE_CASE Then
IsStartsWith = (UCase$(Left$(OperationOnText, Len(TextToMatch))) = UCase$(TextToMatch))
End If
End Function
Public Function Contains(OperationOnText As String, SubString As String, Optional ComparisionType As Comparer = IGNORE_CASE) As Boolean
If ComparisionType = IGNORE_CASE Then
Contains = (InStr(1, OperationOnText, SubString, vbTextCompare) <> 0)
ElseIf ComparisionType = CONSIDER_CASE Then
Contains = (InStr(1, OperationOnText, SubString, vbBinaryCompare) <> 0)
End If
End Function
Public Function BetweenDelimiter(OperationOnText As String, StartDelimiter As String, EndDelimiter As String _
, Optional StartIndex As Long = 1, Optional EndIndex As Long = 1 _
, Optional StartDelimiterSearchDirection As RelativePosition = FROM_START _
, Optional EndDelimiterSearchDirection As RelativePosition = FROM_START) As String
Dim Result As String
Result = AfterDelimiter(OperationOnText, StartDelimiter, StartIndex, StartDelimiterSearchDirection)
Result = BeforeDelimiter(Result, EndDelimiter, EndIndex, EndDelimiterSearchDirection)
BetweenDelimiter = Result
End Function
Public Function AfterDelimiter(OperationOnText As String, Delimiter As String _
, Optional Index As Long = 1, Optional SearchDirection As RelativePosition = FROM_START) As String
If OperationOnText = vbNullString Or Delimiter = vbNullString Then
AfterDelimiter = OperationOnText
Exit Function
End If
Dim AllIndex As Collection
Set AllIndex = FindAllIndexOf(OperationOnText, Delimiter, SearchDirection)
If AllIndex.Count = 0 Then
AfterDelimiter = vbNullString
Exit Function
ElseIf Index > AllIndex.Count Then
Err.Raise 13, "Text.AfterDelimiter", "There is not that much of data"
End If
Dim ItemStartIndex As Long
ItemStartIndex = AllIndex.Item(Index)
If ItemStartIndex = Len(OperationOnText) Then
AfterDelimiter = vbNullString
Else
AfterDelimiter = SubString(OperationOnText, AllIndex.Item(Index) + Len(Delimiter))
End If
End Function
Public Function SubString(GivenText As String, StartIndex As Long, Optional EndIndex As Long = -1) As String
If EndIndex = -1 Then EndIndex = Len(GivenText)
If StartIndex > EndIndex Then
Err.Raise 13, "SubString", "StartIndex need to be less or equal to EndIndex"
End If
SubString = Mid$(GivenText, StartIndex, EndIndex - StartIndex + 1)
End Function
Public Function BeforeDelimiter(OperationOnText As String, Delimiter As String, Optional Index As Long = 1 _
, Optional SearchDirection As RelativePosition = FROM_START) As String
If OperationOnText = vbNullString Or Delimiter = vbNullString Then
BeforeDelimiter = OperationOnText
Exit Function
End If
Dim AllIndex As Collection
Set AllIndex = FindAllIndexOf(OperationOnText, Delimiter, SearchDirection)
If AllIndex.Count = 0 Then
BeforeDelimiter = vbNullString
Exit Function
ElseIf Index > AllIndex.Count Then
Err.Raise 13, "Text.BeforeDelimiter", "There is not that much of data"
End If
BeforeDelimiter = SubString(OperationOnText, 1, AllIndex.Item(Index) - 1)
End Function
Public Function FindAllIndexOf(OperationOnText As String, Delimiter As String, SearchDirection As RelativePosition _
, Optional ComparisionType As Comparer = CONSIDER_CASE) As Collection
Dim AllIndex As Collection
Set AllIndex = New Collection
Dim CurrentIndex As Long
CurrentIndex = 1
Dim TextLength As Long
TextLength = Len(OperationOnText)
Dim ComparingOption As VbCompareMethod
ComparingOption = IIf(ComparisionType = IGNORE_CASE, vbTextCompare, vbBinaryCompare)
CurrentIndex = InStr(1, OperationOnText, Delimiter, ComparingOption)
If CurrentIndex <> 0 Then
AllIndex.Add CurrentIndex
Else
Set FindAllIndexOf = New Collection
Exit Function
End If
Do While (CurrentIndex <= TextLength)
CurrentIndex = InStr(CurrentIndex + Len(Delimiter), OperationOnText, Delimiter, ComparingOption)
If CurrentIndex <> 0 Then
AllIndex.Add CurrentIndex
Else
Exit Do
End If
Loop
If SearchDirection = FROM_START Then
Set FindAllIndexOf = AllIndex
ElseIf SearchDirection = FROM_END Then
Set FindAllIndexOf = ReverseIndexCollection(AllIndex)
Else
Err.Raise 13, "Text.FindAllIndexOf", "Invalid SearchDirection"
End If
End Function
Private Function ReverseIndexCollection(InputCollection As Collection) As Collection
Dim ReversedIndex As Collection
Set ReversedIndex = New Collection
Dim CurrentItemIndex As Long
For CurrentItemIndex = InputCollection.Count To 1 Step -1
ReversedIndex.Add InputCollection.Item(CurrentItemIndex)
Next CurrentItemIndex
Set ReverseIndexCollection = ReversedIndex
End Function
You can just copy-paste the above code and run the “RemoveSpaceFromStepName” sub by passing any workbook object.
Don’t forget to share your thoughts on this. I have tested my code using some complex queries. Let me know if you find any bugs in this.
Here is a sample Input and Output
