sararose27
New Member
- Joined
- Aug 20, 2018
- Messages
- 8
I already have the macro created:
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
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 SubI 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:
