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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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
Help converting Power Query that imports data to VBA
Replies
0
Views
544
The Shy Retirer
T
H
  • Question
Power query trouble
Replies
4
Views
273
hnorgaar
H
L
  • Question
Help win unpivot super slow query
2
Replies
15
Views
959
labello27
L
A
  • Solved
adding extra lines to query removed all steps
Replies
8
Views
327
RoryA
RoryA
V
  • Question
Power Query Split Column Dynamically
Replies
8
Views
823
GraH
GraH

Forum statistics

Threads
1,221,525
Messages
6,160,326
Members
451,637
Latest member
hvp2262

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