Month: March 2023

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 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

Month: March 2023

Replace Space From Power Query Step Name Using VBA

How to Get Downloads Folder Path from Registry Using VBA Win API

GitHub merge conflict for binary (excel) file

Copyright © 2022 | Md. Ismail Hosen

Freelance VBA Developer

Software Engineer, XYZ Cloud BD Ltd.

Quick Links

Resources

Support