Add new power query step to existing VBA

S

sararose27

New Member
Joined
Aug 20, 2018
Messages
8
I already have the macro created:
VBA Code: 
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range([A2].End(xlDown), [A2].End(xlToRight)), , xlNo).Name = _
        "Table1"
    Range("Table1[#All]").Select
    ActiveWorkbook.Queries.Add Name:="Table1", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type any}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Removed Top Rows"" = Table.Skip(#""Changed Type"",1)," & Chr(13) & "" & Chr(10) & "    #""Changed Type1"" = Table.TransformCol" & _
        "umnTypes(#""Removed Top Rows"",{{""Column1"", type text}, {""Column2"", type any}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Removed Columns"" = Table.RemoveColumns(#""Changed Type1"",{""Column1""})," & Chr(13) & "" & Chr(10) & "    #""Renamed Columns"" = Table.RenameColumns(#""Removed Columns"",{{""Column2"", ""Last""}, {""Column3"", ""First""}})," & _
        "" & Chr(13) & "" & Chr(10) & "    #""Split Column by Character Transition"" = Table.SplitColumn(#""Renamed Columns"", ""Column4"", Splitter.SplitTextByCharacterTransition({""0""..""9""}, (c) => not List.Contains({""0""..""9""}, c)), {""Column4.1"", ""Column4.2""})," & Chr(13) & "" & Chr(10) & "    #""Renamed Columns1"" = Table.RenameColumns(#""Split Column by Character Transition"",{{""Column4.1"", ""amt""}})," & Chr(13) & "" & Chr(10) & "    #""Sp" & _
        "lit Column by Character Transition1"" = Table.SplitColumn(#""Renamed Columns1"", ""Column4.2"", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({""0""..""9""}, c), {""0""..""9""}), {""Column4.2.1"", ""Column4.2.2""})," & Chr(13) & "" & Chr(10) & "    #""Renamed Columns2"" = Table.RenameColumns(#""Split Column by Character Transition1"",{{""Column4.2.1"", ""action""}, {""Column" & _
        "4.2.2"", ""EEID""}})," & Chr(13) & "" & Chr(10) & "    #""Split Column by Position"" = Table.SplitColumn(#""Renamed Columns2"", ""Column5"", Splitter.SplitTextByPositions({0, 8}, false), {""Column5.1"", ""Column5.2""})," & Chr(13) & "" & Chr(10) & "    #""Changed Type2"" = Table.TransformColumnTypes(#""Split Column by Position"",{{""amt"", Int64.Type}, {""action"", type text}, {""EEID"", Int64.Type}, {""Column5.1"", Int6" & _
        "4.Type}, {""Column5.2"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Split Column by Position1"" = Table.SplitColumn(#""Changed Type2"", ""Column5.2"", Splitter.SplitTextByPositions({0, 2}, false), {""Column5.2.1"", ""Column5.2.2""})," & Chr(13) & "" & Chr(10) & "    #""Changed Type3"" = Table.TransformColumnTypes(#""Split Column by Position1"",{{""Column5.2.1"", type text}, {""Column5.2.2"", type text}})," & Chr(13) & "" & Chr(10) & "    #""" & _
        "Split Column by Position2"" = Table.SplitColumn(#""Changed Type3"", ""Column5.2.2"", Splitter.SplitTextByPositions({0, 3}, false), {""Column5.2.2.1"", ""Column5.2.2.2""})," & Chr(13) & "" & Chr(10) & "    #""Inserted Date"" = Table.AddColumn(#""Split Column by Position2"", ""Pay End Date"", each Date.From(Text.From([Column5.1], ""en-US"")), type date)," & Chr(13) & "" & Chr(10) & "    #""Reordered Columns"" = Table.Reord" & _
        "erColumns(#""Inserted Date"",{""Last"", ""First"", ""amt"", ""action"", ""EEID"", ""Column5.1"", ""Pay End Date"", ""Column5.2.1"", ""Column5.2.2.1"", ""Column5.2.2.2""})," & Chr(13) & "" & Chr(10) & "    #""Removed Columns1"" = Table.RemoveColumns(#""Reordered Columns"",{""Column5.1""})," & Chr(13) & "" & Chr(10) & "    #""Renamed Columns3"" = Table.RenameColumns(#""Removed Columns1"",{{""Column5.2.1"", ""Pay Sched""}, " & _
        "{""Column5.2.2.1"", ""Pay Group""}, {""Column5.2.2.2"", ""Deduction Code""}})" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Renamed Columns3"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Table1;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table1]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table1_2"
        .Refresh BackgroundQuery:=False
    End With
End Sub

I need to add a step to multiply Renamed Columns 3 by .01. This is the code from power query for just that step: = Table.TransformColumns(#"Renamed Columns3", {{"amt", each _ * 0.01, type number}})
I am having no luck figuring out what portion and how to the code above.

Thanks
 
Last edited by a moderator:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
The easiest way is to take that Power Query table and then Record Marco while adding the new step to the Power Query. Once you Close & Load you can stop recording and then copy the new code and paste it over the old "ActiveWorkbook.Queries.Add Name:"
 
Upvote 0
Try this:

VBA Code: 
    ActiveWorkbook.Queries.Add Name:="Table1", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type any}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Removed Top Rows"" = Table.Skip(#""Changed Type"",1)," & Chr(13) & "" & Chr(10) & "    #""Changed Type1"" = Table.TransformCol" & _
        "umnTypes(#""Removed Top Rows"",{{""Column1"", type text}, {""Column2"", type any}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Removed Columns"" = Table.RemoveColumns(#""Changed Type1"",{""Column1""})," & Chr(13) & "" & Chr(10) & "    #""Renamed Columns"" = Table.RenameColumns(#""Removed Columns"",{{""Column2"", ""Last""}, {""Column3"", ""First""}})," & _
        "" & Chr(13) & "" & Chr(10) & "    #""Split Column by Character Transition"" = Table.SplitColumn(#""Renamed Columns"", ""Column4"", Splitter.SplitTextByCharacterTransition({""0""..""9""}, (c) => not List.Contains({""0""..""9""}, c)), {""Column4.1"", ""Column4.2""})," & Chr(13) & "" & Chr(10) & "    #""Renamed Columns1"" = Table.RenameColumns(#""Split Column by Character Transition"",{{""Column4.1"", ""amt""}})," & Chr(13) & "" & Chr(10) & "    #""Sp" & _
        "lit Column by Character Transition1"" = Table.SplitColumn(#""Renamed Columns1"", ""Column4.2"", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({""0""..""9""}, c), {""0""..""9""}), {""Column4.2.1"", ""Column4.2.2""})," & Chr(13) & "" & Chr(10) & "    #""Renamed Columns2"" = Table.RenameColumns(#""Split Column by Character Transition1"",{{""Column4.2.1"", ""action""}, {""Column" & _
        "4.2.2"", ""EEID""}})," & Chr(13) & "" & Chr(10) & "    #""Split Column by Position"" = Table.SplitColumn(#""Renamed Columns2"", ""Column5"", Splitter.SplitTextByPositions({0, 8}, false), {""Column5.1"", ""Column5.2""})," & Chr(13) & "" & Chr(10) & "    #""Changed Type2"" = Table.TransformColumnTypes(#""Split Column by Position"",{{""amt"", Int64.Type}, {""action"", type text}, {""EEID"", Int64.Type}, {""Column5.1"", Int6" & _
        "4.Type}, {""Column5.2"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Split Column by Position1"" = Table.SplitColumn(#""Changed Type2"", ""Column5.2"", Splitter.SplitTextByPositions({0, 2}, false), {""Column5.2.1"", ""Column5.2.2""})," & Chr(13) & "" & Chr(10) & "    #""Changed Type3"" = Table.TransformColumnTypes(#""Split Column by Position1"",{{""Column5.2.1"", type text}, {""Column5.2.2"", type text}})," & Chr(13) & "" & Chr(10) & "    #""" & _
        "Split Column by Position2"" = Table.SplitColumn(#""Changed Type3"", ""Column5.2.2"", Splitter.SplitTextByPositions({0, 3}, false), {""Column5.2.2.1"", ""Column5.2.2.2""})," & Chr(13) & "" & Chr(10) & "    #""Inserted Date"" = Table.AddColumn(#""Split Column by Position2"", ""Pay End Date"", each Date.From(Text.From([Column5.1], ""en-US"")), type date)," & Chr(13) & "" & Chr(10) & "    #""Reordered Columns"" = Table.Reord" & _
        "erColumns(#""Inserted Date"",{""Last"", ""First"", ""amt"", ""action"", ""EEID"", ""Column5.1"", ""Pay End Date"", ""Column5.2.1"", ""Column5.2.2.1"", ""Column5.2.2.2""})," & Chr(13) & "" & Chr(10) & "    #""Removed Columns1"" = Table.RemoveColumns(#""Reordered Columns"",{""Column5.1""})," & Chr(13) & "" & Chr(10) & "    #""Renamed Columns3"" = Table.RenameColumns(#""Removed Columns1"",{{""Column5.2.1"", ""Pay Sched""}, " & _
        "{""Column5.2.2.1"", ""Pay Group""}, {""Column5.2.2.2"", ""Deduction Code""}}), " & Chr(13) & "" & Chr(10) & "    Result = Table.TransformColumns(#""Renamed Columns3"", {{""amt"", each _ * 0.01, type number}}) " & "" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Result"

The recommended coding format in the module to be able to modify it easily in the future:
VBA Code: 
ActiveWorkbook.Queries.Add Name:="Table1", Formula:= _
"let" & vbCrLf & _
"    Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," & vbCrLf & _
"    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type any}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}})," & vbCrLf & _
"    #""Removed Top Rows"" = Table.Skip(#""Changed Type"",1)," & vbCrLf & _
"    #""Changed Type1"" = Table.TransformColumnTypes(#""Removed Top Rows"",{{""Column1"", type text}, {""Column2"", type any}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}})," & vbCrLf & _
"    #""Removed Columns"" = Table.RemoveColumns(#""Changed Type1"",{""Column1""})," & vbCrLf & _
"    #""Renamed Columns"" = Table.RenameColumns(#""Removed Columns"",{{""Column2"", ""Last""}, {""Column3"", ""First""}})," & vbCrLf & _
"    #""Split Column by Character Transition"" = Table.SplitColumn(#""Renamed Columns"", ""Column4"", Splitter.SplitTextByCharacterTransition({""0""..""9""}, (c) => not List.Contains({""0""..""9""}, c)), {""Column4.1"", ""Column4.2""})," & vbCrLf & _
"    #""Renamed Columns1"" = Table.RenameColumns(#""Split Column by Character Transition"",{{""Column4.1"", ""amt""}})," & vbCrLf & _
"    #""Split Column by Character Transition1"" = Table.SplitColumn(#""Renamed Columns1"", ""Column4.2"", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({""0""..""9""}, c), {""0""..""9""}), {""Column4.2.1"", ""Column4.2.2""})," & vbCrLf & _
"    #""Renamed Columns2"" = Table.RenameColumns(#""Split Column by Character Transition1"",{{""Column4.2.1"", ""action""}, {""Column4.2.2"", ""EEID""}})," & vbCrLf & _
"    #""Split Column by Position"" = Table.SplitColumn(#""Renamed Columns2"", ""Column5"", Splitter.SplitTextByPositions({0, 8}, false), {""Column5.1"", ""Column5.2""})," & vbCrLf & _
"    #""Changed Type2"" = Table.TransformColumnTypes(#""Split Column by Position"",{{""amt"", Int64.Type}, {""action"", type text}, {""EEID"", Int64.Type}, {""Column5.1"", Int64.Type}, {""Column5.2"", type text}})," & vbCrLf & _
"    #""Split Column by Position1"" = Table.SplitColumn(#""Changed Type2"", ""Column5.2"", Splitter.SplitTextByPositions({0, 2}, false), {""Column5.2.1"", ""Column5.2.2""})," & vbCrLf & _
"    #""Changed Type3"" = Table.TransformColumnTypes(#""Split Column by Position1"",{{""Column5.2.1"", type text}, {""Column5.2.2"", type text}})," & vbCrLf & _
"    #""Split Column by Position2"" = Table.SplitColumn(#""Changed Type3"", ""Column5.2.2"", Splitter.SplitTextByPositions({0, 3}, false), {""Column5.2.2.1"", ""Column5.2.2.2""})," & vbCrLf & "    #""Inserted Date"" = Table.AddColumn(#""Split Column by Position2"", ""Pay End Date"", each Date.From(Text.From([Column5.1], ""en-US"")), type date)," & vbCrLf & _
"    #""Reordered Columns"" = Table.ReorderColumns(#""Inserted Date"",{""Last"", ""First"", ""amt"", ""action"", ""EEID"", ""Column5.1"", ""Pay End Date"", ""Column5.2.1"", ""Column5.2.2.1"", ""Column5.2.2.2""})," & vbCrLf & _
"    #""Removed Columns1"" = Table.RemoveColumns(#""Reordered Columns"",{""Column5.1""})," & vbCrLf & _
"    #""Renamed Columns3"" = Table.RenameColumns(#""Removed Columns1"",{{""Column5.2.1"", ""Pay Sched""}, {""Column5.2.2.1"", ""Pay Group""}, {""Column5.2.2.2"", ""Deduction Code""}})," & vbCrLf & _
"    Result = Table.TransformColumns(#""Renamed Columns3"", {{""amt"", each _ * 0.01, type number}}) " & vbCrLf & "" & _
"in" & vbCrLf & _
"    Result"

Extra one-liner tool for future use: The following might help generating VBA code from the Power Query M code.
VBA Code: 
Sub convertMCodeToVBA()
    Debug.Print """" & Replace(Replace(ThisWorkbook.Queries(1).Formula, """", """"""), vbCrLf, """ & vbcrlf & _" & vbCrLf & """") & """"
End Sub
 
Last edited:
Upvote 0
Solution
smozgur said:
Try this:

VBA Code: 
    ActiveWorkbook.Queries.Add Name:="Table1", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type any}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Removed Top Rows"" = Table.Skip(#""Changed Type"",1)," & Chr(13) & "" & Chr(10) & "    #""Changed Type1"" = Table.TransformCol" & _
        "umnTypes(#""Removed Top Rows"",{{""Column1"", type text}, {""Column2"", type any}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Removed Columns"" = Table.RemoveColumns(#""Changed Type1"",{""Column1""})," & Chr(13) & "" & Chr(10) & "    #""Renamed Columns"" = Table.RenameColumns(#""Removed Columns"",{{""Column2"", ""Last""}, {""Column3"", ""First""}})," & _
        "" & Chr(13) & "" & Chr(10) & "    #""Split Column by Character Transition"" = Table.SplitColumn(#""Renamed Columns"", ""Column4"", Splitter.SplitTextByCharacterTransition({""0""..""9""}, (c) => not List.Contains({""0""..""9""}, c)), {""Column4.1"", ""Column4.2""})," & Chr(13) & "" & Chr(10) & "    #""Renamed Columns1"" = Table.RenameColumns(#""Split Column by Character Transition"",{{""Column4.1"", ""amt""}})," & Chr(13) & "" & Chr(10) & "    #""Sp" & _
        "lit Column by Character Transition1"" = Table.SplitColumn(#""Renamed Columns1"", ""Column4.2"", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({""0""..""9""}, c), {""0""..""9""}), {""Column4.2.1"", ""Column4.2.2""})," & Chr(13) & "" & Chr(10) & "    #""Renamed Columns2"" = Table.RenameColumns(#""Split Column by Character Transition1"",{{""Column4.2.1"", ""action""}, {""Column" & _
        "4.2.2"", ""EEID""}})," & Chr(13) & "" & Chr(10) & "    #""Split Column by Position"" = Table.SplitColumn(#""Renamed Columns2"", ""Column5"", Splitter.SplitTextByPositions({0, 8}, false), {""Column5.1"", ""Column5.2""})," & Chr(13) & "" & Chr(10) & "    #""Changed Type2"" = Table.TransformColumnTypes(#""Split Column by Position"",{{""amt"", Int64.Type}, {""action"", type text}, {""EEID"", Int64.Type}, {""Column5.1"", Int6" & _
        "4.Type}, {""Column5.2"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Split Column by Position1"" = Table.SplitColumn(#""Changed Type2"", ""Column5.2"", Splitter.SplitTextByPositions({0, 2}, false), {""Column5.2.1"", ""Column5.2.2""})," & Chr(13) & "" & Chr(10) & "    #""Changed Type3"" = Table.TransformColumnTypes(#""Split Column by Position1"",{{""Column5.2.1"", type text}, {""Column5.2.2"", type text}})," & Chr(13) & "" & Chr(10) & "    #""" & _
        "Split Column by Position2"" = Table.SplitColumn(#""Changed Type3"", ""Column5.2.2"", Splitter.SplitTextByPositions({0, 3}, false), {""Column5.2.2.1"", ""Column5.2.2.2""})," & Chr(13) & "" & Chr(10) & "    #""Inserted Date"" = Table.AddColumn(#""Split Column by Position2"", ""Pay End Date"", each Date.From(Text.From([Column5.1], ""en-US"")), type date)," & Chr(13) & "" & Chr(10) & "    #""Reordered Columns"" = Table.Reord" & _
        "erColumns(#""Inserted Date"",{""Last"", ""First"", ""amt"", ""action"", ""EEID"", ""Column5.1"", ""Pay End Date"", ""Column5.2.1"", ""Column5.2.2.1"", ""Column5.2.2.2""})," & Chr(13) & "" & Chr(10) & "    #""Removed Columns1"" = Table.RemoveColumns(#""Reordered Columns"",{""Column5.1""})," & Chr(13) & "" & Chr(10) & "    #""Renamed Columns3"" = Table.RenameColumns(#""Removed Columns1"",{{""Column5.2.1"", ""Pay Sched""}, " & _
        "{""Column5.2.2.1"", ""Pay Group""}, {""Column5.2.2.2"", ""Deduction Code""}}), " & Chr(13) & "" & Chr(10) & "    Result = Table.TransformColumns(#""Renamed Columns3"", {{""amt"", each _ * 0.01, type number}}) " & "" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Result"

The recommended coding format in the module to be able to modify it easily in the future:
VBA Code: 
ActiveWorkbook.Queries.Add Name:="Table1", Formula:= _
"let" & vbCrLf & _
"    Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," & vbCrLf & _
"    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type any}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}})," & vbCrLf & _
"    #""Removed Top Rows"" = Table.Skip(#""Changed Type"",1)," & vbCrLf & _
"    #""Changed Type1"" = Table.TransformColumnTypes(#""Removed Top Rows"",{{""Column1"", type text}, {""Column2"", type any}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}})," & vbCrLf & _
"    #""Removed Columns"" = Table.RemoveColumns(#""Changed Type1"",{""Column1""})," & vbCrLf & _
"    #""Renamed Columns"" = Table.RenameColumns(#""Removed Columns"",{{""Column2"", ""Last""}, {""Column3"", ""First""}})," & vbCrLf & _
"    #""Split Column by Character Transition"" = Table.SplitColumn(#""Renamed Columns"", ""Column4"", Splitter.SplitTextByCharacterTransition({""0""..""9""}, (c) => not List.Contains({""0""..""9""}, c)), {""Column4.1"", ""Column4.2""})," & vbCrLf & _
"    #""Renamed Columns1"" = Table.RenameColumns(#""Split Column by Character Transition"",{{""Column4.1"", ""amt""}})," & vbCrLf & _
"    #""Split Column by Character Transition1"" = Table.SplitColumn(#""Renamed Columns1"", ""Column4.2"", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({""0""..""9""}, c), {""0""..""9""}), {""Column4.2.1"", ""Column4.2.2""})," & vbCrLf & _
"    #""Renamed Columns2"" = Table.RenameColumns(#""Split Column by Character Transition1"",{{""Column4.2.1"", ""action""}, {""Column4.2.2"", ""EEID""}})," & vbCrLf & _
"    #""Split Column by Position"" = Table.SplitColumn(#""Renamed Columns2"", ""Column5"", Splitter.SplitTextByPositions({0, 8}, false), {""Column5.1"", ""Column5.2""})," & vbCrLf & _
"    #""Changed Type2"" = Table.TransformColumnTypes(#""Split Column by Position"",{{""amt"", Int64.Type}, {""action"", type text}, {""EEID"", Int64.Type}, {""Column5.1"", Int64.Type}, {""Column5.2"", type text}})," & vbCrLf & _
"    #""Split Column by Position1"" = Table.SplitColumn(#""Changed Type2"", ""Column5.2"", Splitter.SplitTextByPositions({0, 2}, false), {""Column5.2.1"", ""Column5.2.2""})," & vbCrLf & _
"    #""Changed Type3"" = Table.TransformColumnTypes(#""Split Column by Position1"",{{""Column5.2.1"", type text}, {""Column5.2.2"", type text}})," & vbCrLf & _
"    #""Split Column by Position2"" = Table.SplitColumn(#""Changed Type3"", ""Column5.2.2"", Splitter.SplitTextByPositions({0, 3}, false), {""Column5.2.2.1"", ""Column5.2.2.2""})," & vbCrLf & "    #""Inserted Date"" = Table.AddColumn(#""Split Column by Position2"", ""Pay End Date"", each Date.From(Text.From([Column5.1], ""en-US"")), type date)," & vbCrLf & _
"    #""Reordered Columns"" = Table.ReorderColumns(#""Inserted Date"",{""Last"", ""First"", ""amt"", ""action"", ""EEID"", ""Column5.1"", ""Pay End Date"", ""Column5.2.1"", ""Column5.2.2.1"", ""Column5.2.2.2""})," & vbCrLf & _
"    #""Removed Columns1"" = Table.RemoveColumns(#""Reordered Columns"",{""Column5.1""})," & vbCrLf & _
"    #""Renamed Columns3"" = Table.RenameColumns(#""Removed Columns1"",{{""Column5.2.1"", ""Pay Sched""}, {""Column5.2.2.1"", ""Pay Group""}, {""Column5.2.2.2"", ""Deduction Code""}})," & vbCrLf & _
"    Result = Table.TransformColumns(#""Renamed Columns3"", {{""amt"", each _ * 0.01, type number}}) " & vbCrLf & "" & _
"in" & vbCrLf & _
"    Result"

Extra one-liner tool for future use: The following might help generating VBA code from the Power Query M code.
VBA Code: 
Sub convertMCodeToVBA()
    Debug.Print """" & Replace(Replace(ThisWorkbook.Queries(1).Formula, """", """"""), vbCrLf, """ & vbcrlf & _" & vbCrLf & """") & """"
End Sub
Click to expand...
Brilliant. Thank you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Help converting Power Query that imports data to VBA
Replies
0
Views
606
The Shy Retirer
T
H
  • Question Question
Power query trouble
Replies
4
Views
335
hnorgaar
H
L
  • Question Question
Help win unpivot super slow query
2
Replies
15
Views
1K
labello27
L
A
  • Solved
adding extra lines to query removed all steps
Replies
8
Views
374
RoryA
RoryA
R
  • Question Question
Power Query Help to Calculate Stock Splits
Replies
0
Views
157
rasinc
R

Forum statistics

Threads
1,223,877
Messages
6,175,138
Members
452,614
Latest member
MRSWIN2709

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top