I need to pass the following Power Query M through my VBA Script:
let Source = Excel.CurrentWorkbook(){[Name="C192767TO15_D3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type any}, {"Process Date", type datetime}, {"From", type datetime}, {"To", type datetime}, {"This Invoice", type number}, {"Earned To-Date", type number}, {"Rem. To-Date", type number}, {"% Rem.", type number}, {"Local Account", type text}, {"Local Amount", Int64.Type}, {"Grant Account", type text}, {"Grant Amount", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [#"Invoice #"] <> null),
#"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Item", "Process Date", "Invoice #", "From", "To", "This Invoice", "Earned To-Date", "Rem. To-Date", "% Rem.", "Local Account", "Local Amount", "Grant Account", "Grant Amount"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns", {{"Grant Amount", type text}}, "en-US"),{"Grant Account", "Grant Amount"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Account:Amount"),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Local Amount", type text}}, "en-US"),{"Local Account", "Local Amount"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Account:Amount.2"),
#"Renamed Columns" = Table.RenameColumns(#"Merged Columns1",{{"Account:Amount", "Account:Amount.1"}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Item", "Process Date", "Invoice #", "From", "To", "This Invoice", "Earned To-Date", "Rem. To-Date", "% Rem."}, "Attribute", "Value"),
#"Reordered Columns1" = Table.ReorderColumns(#"Unpivoted Columns",{"Item", "Process Date", "Invoice #", "From", "To", "This Invoice", "Earned To-Date", "Rem. To-Date", "% Rem.", "Value", "Attribute"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns1",{"Attribute"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Value", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Value.1", "Value.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type number}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Value.1", "Account"}, {"Value.2", "Amount"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"This Invoice", "Earned To-Date", "Rem. To-Date", "% Rem."}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Amount", Currency.Type}}),
#"Removed Columns2" = Table.RemoveColumns(#"Changed Type2",{"Item"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns2", "Source", each "C192767TO15_D3"),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Added Custom", "Source", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Source.1", "Source.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Source.1", type text}, {"Source.2", type text}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type3",{{"Source.2", "Activity"}, {"Source.1", "Contract"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns2","TO",",TO",Replacer.ReplaceText,{"Contract"}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Replaced Value", "Contract", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Contract.1", "Contract.2"}),
#"Split Column by Position" = Table.SplitColumn(#"Split Column by Delimiter2", "Contract.1", Splitter.SplitTextByPositions({0, 4}, true), {"Contract.1.1", "Contract.1.2"}),
#"Added Custom1" = Table.AddColumn(#"Split Column by Position", "Custom", each "PW,"),
#"Reordered Columns2" = Table.ReorderColumns(#"Added Custom1",{"Process Date", "Invoice #", "From", "To", "Account", "Amount", "Contract.1.1", "Contract.1.2", "Custom", "Contract.2", "Activity"}),
#"Merged Columns2" = Table.CombineColumns(#"Reordered Columns2",{"Contract.1.1", "Contract.1.2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"ctr1"),
#"Inserted Merged Column" = Table.AddColumn(#"Merged Columns2", "ctr2", each Text.Combine({[ctr1], [Custom]}, "-"), type text),
#"Removed Columns3" = Table.RemoveColumns(#"Inserted Merged Column",{"ctr1", "Custom"}),
#"Reordered Columns3" = Table.ReorderColumns(#"Removed Columns3",{"Process Date", "Invoice #", "From", "To", "Account", "Amount", "ctr2", "Contract.2", "Activity"}),
#"Inserted Merged Column1" = Table.AddColumn(#"Reordered Columns3", "Contract", each Text.Combine({[ctr2], [Contract.2]}, ""), type text),
#"Removed Columns4" = Table.RemoveColumns(#"Inserted Merged Column1",{"ctr2", "Contract.2"}),
#"Reordered Columns4" = Table.ReorderColumns(#"Removed Columns4",{"Process Date", "Invoice #", "From", "To", "Account", "Amount", "Contract", "Activity"}),
#"Replaced Value1" = Table.ReplaceValue(#"Reordered Columns4","D0","PRE-DES",Replacer.ReplaceText,{"Activity"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","D1","DESIGN",Replacer.ReplaceText,{"Activity"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","D3","PST-DES",Replacer.ReplaceText,{"Activity"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","R","ROW",Replacer.ReplaceText,{"Activity"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","CS","CEI",Replacer.ReplaceText,{"Activity"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","CC","CONST",Replacer.ReplaceText,{"Activity"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","MT","MATER",Replacer.ReplaceText,{"Activity"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Replaced Value7",{{"Invoice #", type text}})
in #"Changed Type4"`
I Tried Replacing values as follows:
" & Chr(34) & " replace "
" & Chr(35) & " replace #
" & Chr(123) & " replace {
" & Chr(125) & " replace }
" & Chr(91) & " replace [
" & Chr(93) & " replace ]
" & Chr(59) & " replace ;
" & Chr(58) & " replace :
" & Chr(60) & " replace <
" & Chr(62) & " replace >
This created the following:
"let Source " & Chr(61) & " Excel.CurrentWorkbook()" & Chr(123) & "" & Chr(91) & "Name" & Chr(61) & "" & Chr(34) & "C192767TO15_D3" & Chr(34) & "" & Chr(93) & "" & Chr(125) & "" & Chr(91) & "Content" & Chr(93) & ", " & Chr(35) & "" & Chr(34) & "Changed Type" & Chr(34) & " " & Chr(61) & " Table.TransformColumnTypes(Source," & Chr(123) & "" & Chr(123) & "" & Chr(34) & "Item" & Chr(34) & ", type any" & Chr(125) & ", " & Chr(123) & "" & Chr(34) & "Process Date" & Chr(34) & ", type datetime" & Chr(125) & ", " & Chr(123) & "" & Chr(34) & "From" & Chr(34) & ", type datetime" & Chr(125) & ", " & Chr(123) & "" & Chr(34) & "To" & Chr(34) & ", type datetime" & Chr(125) & ", " & Chr(123) & "" & Chr(34) & "This Invoice" & Chr(34) & ", type number" & Chr(125) & ", " & Chr(123) & "" & Chr(34) & "Earned To-Date" & Chr(34) & ", type number" & Chr(125) & ", " & Chr(123) & "" & Chr(34) & "Rem. To-Date" & Chr(34) & ", type number" & Chr(125) & ", " & Chr(123) & "" & Chr(34) & "% Rem." & Chr(34) & ", type number" & Chr(125) & ", " & Chr(123) & "" & Chr(34) & "Local Account" & Chr(34) & ", type text" & Chr(125) & ", " & Chr(123) & "" & Chr(34) & "Local Amount" & Chr(34) & ", Int64.Type" & Chr(125) & ", " & Chr(123) & "" & Chr(34) & "Grant Account" & Chr(34) & ", type text" & Chr(125) & ", " & Chr(123) & "" & Chr(34) & "Grant Amount" & Chr(34) & ", type number" & Chr(125) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Filtered Rows" & Chr(34) & " " & Chr(61) & " Table.SelectRows(" & Chr(35) & "" & Chr(34) & "Changed Type" & Chr(34) & ", each " & Chr(91) & "" & Chr(35) & "" & Chr(34) & "Invoice " & Chr(35) & "" & Chr(34) & "" & Chr(93) & " " & Chr(60) & "" & Chr(62) & " null), " & Chr(35) & "" & Chr(34) & "Reordered Columns" & Chr(34) & " " & Chr(61) & " Table.ReorderColumns(" & Chr(35) & "" & Chr(34) & "Filtered Rows" & Chr(34) & "," & Chr(123) & "" & Chr(34) & "Item" & Chr(34) & ", " & Chr(34) & "Process Date" & Chr(34) & ", " & Chr(34) & "Invoice " & Chr(35) & "" & Chr(34) & ", " & Chr(34) & "From" & Chr(34) & ", " & Chr(34) & "To" & Chr(34) & ", " & Chr(34) & "This Invoice" & Chr(34) & ", " & Chr(34) & "Earned To-Date" & Chr(34) & ", " & Chr(34) & "Rem. To-Date" & Chr(34) & ", " & Chr(34) & "% Rem." & Chr(34) & ", " & Chr(34) & "Local Account" & Chr(34) & ", " & Chr(34) & "Local Amount" & Chr(34) & ", " & Chr(34) & "Grant Account" & Chr(34) & ", " & Chr(34) & "Grant Amount" & Chr(34) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Merged Columns" & Chr(34) & " " & Chr(61) & " Table.CombineColumns(Table.TransformColumnTypes(" & Chr(35) & "" & Chr(34) & "Reordered Columns" & Chr(34) & ", " & Chr(123) & "" & Chr(123) & "" & Chr(34) & "Grant Amount" & Chr(34) & ", type text" & Chr(125) & "" & Chr(125) & ", " & Chr(34) & "en-US" & Chr(34) & ")," & Chr(123) & "" & Chr(34) & "Grant Account" & Chr(34) & ", " & Chr(34) & "Grant Amount" & Chr(34) & "" & Chr(125) & ",Combiner.CombineTextByDelimiter(" & Chr(34) & "" & Chr(59) & "" & Chr(34) & ", QuoteStyle.None)," & Chr(34) & "Account" & Chr(58) & "Amount" & Chr(34) & "), " & Chr(35) & "" & Chr(34) & "Merged Columns1" & Chr(34) & " " & Chr(61) & " Table.CombineColumns(Table.TransformColumnTypes(" & Chr(35) & "" & Chr(34) & "Merged Columns" & Chr(34) & ", " & Chr(123) & "" & Chr(123) & "" & Chr(34) & "Local Amount" & Chr(34) & ", type text" & Chr(125) & "" & Chr(125) & ", " & Chr(34) & "en-US" & Chr(34) & ")," & Chr(123) & "" & Chr(34) & "Local Account" & Chr(34) & ", " & Chr(34) & "Local Amount" & Chr(34) & "" & Chr(125) & ",Combiner.CombineTextByDelimiter(" & Chr(34) & "" & Chr(59) & "" & Chr(34) & ", QuoteStyle.None)," & Chr(34) & "Account" & Chr(58) & "Amount.2" & Chr(34) & "), " & Chr(35) & "" & Chr(34) & "Renamed Columns" & Chr(34) & " " & Chr(61) & " Table.RenameColumns(" & Chr(35) & "" & Chr(34) & "Merged Columns1" & Chr(34) & "," & Chr(123) & "" & Chr(123) & "" & Chr(34) & "Account" & Chr(58) & "Amount" & Chr(34) & ", " & Chr(34) & "Account" & Chr(58) & "Amount.1" & Chr(34) & "" & Chr(125) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Unpivoted Columns" & Chr(34) & " " & Chr(61) & " Table.UnpivotOtherColumns(" & Chr(35) & "" & Chr(34) & "Renamed Columns" & Chr(34) & ", " & Chr(123) & "" & Chr(34) & "Item" & Chr(34) & ", " & Chr(34) & "Process Date" & Chr(34) & ", " & Chr(34) & "Invoice " & Chr(35) & "" & Chr(34) & ", " & Chr(34) & "From" & Chr(34) & ", " & Chr(34) & "To" & Chr(34) & ", " & Chr(34) & "This Invoice" & Chr(34) & ", " & Chr(34) & "Earned To-Date" & Chr(34) & ", " & Chr(34) & "Rem. To-Date" & Chr(34) & ", " & Chr(34) & "% Rem." & Chr(34) & "" & Chr(125) & ", " & Chr(34) & "Attribute" & Chr(34) & ", " & Chr(34) & "Value" & Chr(34) & "), " & Chr(35) & "" & Chr(34) & "Reordered Columns1" & Chr(34) & " " & Chr(61) & " Table.ReorderColumns(" & Chr(35) & "" & Chr(34) & "Unpivoted Columns" & Chr(34) & "," & Chr(123) & "" & Chr(34) & "Item" & Chr(34) & ", " & Chr(34) & "Process Date" & Chr(34) & ", " & Chr(34) & "Invoice " & Chr(35) & "" & Chr(34) & ", " & Chr(34) & "From" & Chr(34) & ", " & Chr(34) & "To" & Chr(34) & ", " & Chr(34) & "This Invoice" & Chr(34) & ", " & Chr(34) & "Earned To-Date" & Chr(34) & ", " & Chr(34) & "Rem. To-Date" & Chr(34) & ", " & Chr(34) & "% Rem." & Chr(34) & ", " & Chr(34) & "Value" & Chr(34) & ", " & Chr(34) & "Attribute" & Chr(34) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Removed Columns" & Chr(34) & " " & Chr(61) & " Table.RemoveColumns(" & Chr(35) & "" & Chr(34) & "Reordered Columns1" & Chr(34) & "," & Chr(123) & "" & Chr(34) & "Attribute" & Chr(34) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Split Column by Delimiter" & Chr(34) & " " & Chr(61) & " Table.SplitColumn(" & Chr(35) & "" & Chr(34) & "Removed Columns" & Chr(34) & ", " & Chr(34) & "Value" & Chr(34) & ", Splitter.SplitTextByDelimiter(" & Chr(34) & "" & Chr(59) & "" & Chr(34) & ", QuoteStyle.Csv), " & Chr(123) & "" & Chr(34) & "Value.1" & Chr(34) & ", " & Chr(34) & "Value.2" & Chr(34) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Changed Type1" & Chr(34) & " " & Chr(61) & " Table.TransformColumnTypes(" & Chr(35) & "" & Chr(34) & "Split Column by Delimiter" & Chr(34) & "," & Chr(123) & "" & Chr(123) & "" & Chr(34) & "Value.1" & Chr(34) & ", type text" & Chr(125) & ", " & Chr(123) & "" & Chr(34) & "Value.2" & Chr(34) & ", type number" & Chr(125) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Renamed Columns1" & Chr(34) & " " & Chr(61) & " Table.RenameColumns(" & Chr(35) & "" & Chr(34) & "Changed Type1" & Chr(34) & "," & Chr(123) & "" & Chr(123) & "" & Chr(34) & "Value.1" & Chr(34) & ", " & Chr(34) & "Account" & Chr(34) & "" & Chr(125) & ", " & Chr(123) & "" & Chr(34) & "Value.2" & Chr(34) & ", " & Chr(34) & "Amount" & Chr(34) & "" & Chr(125) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Removed Columns1" & Chr(34) & " " & Chr(61) & " Table.RemoveColumns(" & Chr(35) & "" & Chr(34) & "Renamed Columns1" & Chr(34) & "," & Chr(123) & "" & Chr(34) & "This Invoice" & Chr(34) & ", " & Chr(34) & "Earned To-Date" & Chr(34) & ", " & Chr(34) & "Rem. To-Date" & Chr(34) & ", " & Chr(34) & "% Rem." & Chr(34) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Changed Type2" & Chr(34) & " " & Chr(61) & " Table.TransformColumnTypes(" & Chr(35) & "" & Chr(34) & "Removed Columns1" & Chr(34) & "," & Chr(123) & "" & Chr(123) & "" & Chr(34) & "Amount" & Chr(34) & ", Currency.Type" & Chr(125) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Removed Columns2" & Chr(34) & " " & Chr(61) & " Table.RemoveColumns(" & Chr(35) & "" & Chr(34) & "Changed Type2" & Chr(34) & "," & Chr(123) & "" & Chr(34) & "Item" & Chr(34) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Added Custom" & Chr(34) & " " & Chr(61) & " Table.AddColumn(" & Chr(35) & "" & Chr(34) & "Removed Columns2" & Chr(34) & ", " & Chr(34) & "Source" & Chr(34) & ", each " & Chr(34) & "C192767TO15_D3" & Chr(34) & "), " & Chr(35) & "" & Chr(34) & "Split Column by Delimiter1" & Chr(34) & " " & Chr(61) & " Table.SplitColumn(" & Chr(35) & "" & Chr(34) & "Added Custom" & Chr(34) & ", " & Chr(34) & "Source" & Chr(34) & ", Splitter.SplitTextByDelimiter(" & Chr(34) & "_" & Chr(34) & ", QuoteStyle.Csv), " & Chr(123) & "" & Chr(34) & "Source.1" & Chr(34) & ", " & Chr(34) & "Source.2" & Chr(34) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Changed Type3" & Chr(34) & " " & Chr(61) & " Table.TransformColumnTypes(" & Chr(35) & "" & Chr(34) & "Split Column by Delimiter1" & Chr(34) & "," & Chr(123) & "" & Chr(123) & "" & Chr(34) & "Source.1" & Chr(34) & ", type text" & Chr(125) & ", " & Chr(123) & "" & Chr(34) & "Source.2" & Chr(34) & ", type text" & Chr(125) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Renamed Columns2" & Chr(34) & " " & Chr(61) & " Table.RenameColumns(" & Chr(35) & "" & Chr(34) & "Changed Type3" & Chr(34) & "," & Chr(123) & "" & Chr(123) & "" & Chr(34) & "Source.2" & Chr(34) & ", " & Chr(34) & "Activity" & Chr(34) & "" & Chr(125) & ", " & Chr(123) & "" & Chr(34) & "Source.1" & Chr(34) & ", " & Chr(34) & "Contract" & Chr(34) & "" & Chr(125) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Replaced Value" & Chr(34) & " " & Chr(61) & " Table.ReplaceValue(" & Chr(35) & "" & Chr(34) & "Renamed Columns2" & Chr(34) & "," & Chr(34) & "TO" & Chr(34) & "," & Chr(34) & ",TO" & Chr(34) & ",Replacer.ReplaceText," & Chr(123) & "" & Chr(34) & "Contract" & Chr(34) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Split Column by Delimiter2" & Chr(34) & " " & Chr(61) & " Table.SplitColumn(" & Chr(35) & "" & Chr(34) & "Replaced Value" & Chr(34) & ", " & Chr(34) & "Contract" & Chr(34) & ", Splitter.SplitTextByDelimiter(" & Chr(34) & "," & Chr(34) & ", QuoteStyle.Csv), " & Chr(123) & "" & Chr(34) & "Contract.1" & Chr(34) & ", " & Chr(34) & "Contract.2" & Chr(34) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Split Column by Position" & Chr(34) & " " & Chr(61) & " Table.SplitColumn(" & Chr(35) & "" & Chr(34) & "Split Column by Delimiter2" & Chr(34) & ", " & Chr(34) & "Contract.1" & Chr(34) & ", Splitter.SplitTextByPositions(" & Chr(123) & "0, 4" & Chr(125) & ", true), " & Chr(123) & "" & Chr(34) & "Contract.1.1" & Chr(34) & ", " & Chr(34) & "Contract.1.2" & Chr(34) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Added Custom1" & Chr(34) & " " & Chr(61) & " Table.AddColumn(" & Chr(35) & "" & Chr(34) & "Split Column by Position" & Chr(34) & ", " & Chr(34) & "Custom" & Chr(34) & ", each " & Chr(34) & "PW," & Chr(34) & "), " & Chr(35) & "" & Chr(34) & "Reordered Columns2" & Chr(34) & " " & Chr(61) & " Table.ReorderColumns(" & Chr(35) & "" & Chr(34) & "Added Custom1" & Chr(34) & "," & Chr(123) & "" & Chr(34) & "Process Date" & Chr(34) & ", " & Chr(34) & "Invoice " & Chr(35) & "" & Chr(34) & ", " & Chr(34) & "From" & Chr(34) & ", " & Chr(34) & "To" & Chr(34) & ", " & Chr(34) & "Account" & Chr(34) & ", " & Chr(34) & "Amount" & Chr(34) & ", " & Chr(34) & "Contract.1.1" & Chr(34) & ", " & Chr(34) & "Contract.1.2" & Chr(34) & ", " & Chr(34) & "Custom" & Chr(34) & ", " & Chr(34) & "Contract.2" & Chr(34) & ", " & Chr(34) & "Activity" & Chr(34) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Merged Columns2" & Chr(34) & " " & Chr(61) & " Table.CombineColumns(" & Chr(35) & "" & Chr(34) & "Reordered Columns2" & Chr(34) & "," & Chr(123) & "" & Chr(34) & "Contract.1.1" & Chr(34) & ", " & Chr(34) & "Contract.1.2" & Chr(34) & "" & Chr(125) & ",Combiner.CombineTextByDelimiter(" & Chr(34) & "-" & Chr(34) & ", QuoteStyle.None)," & Chr(34) & "ctr1" & Chr(34) & "), " & Chr(35) & "" & Chr(34) & "Inserted Merged Column" & Chr(34) & " " & Chr(61) & " Table.AddColumn(" & Chr(35) & "" & Chr(34) & "Merged Columns2" & Chr(34) & ", " & Chr(34) & "ctr2" & Chr(34) & ", each Text.Combine(" & Chr(123) & "" & Chr(91) & "ctr1" & Chr(93) & ", " & Chr(91) & "Custom" & Chr(93) & "" & Chr(125) & ", " & Chr(34) & "-" & Chr(34) & "), type text), " & Chr(35) & "" & Chr(34) & "Removed Columns3" & Chr(34) & " " & Chr(61) & " Table.RemoveColumns(" & Chr(35) & "" & Chr(34) & "Inserted Merged Column" & Chr(34) & "," & Chr(123) & "" & Chr(34) & "ctr1" & Chr(34) & ", " & Chr(34) & "Custom" & Chr(34) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Reordered Columns3" & Chr(34) & " " & Chr(61) & " Table.ReorderColumns(" & Chr(35) & "" & Chr(34) & "Removed Columns3" & Chr(34) & "," & Chr(123) & "" & Chr(34) & "Process Date" & Chr(34) & ", " & Chr(34) & "Invoice " & Chr(35) & "" & Chr(34) & ", " & Chr(34) & "From" & Chr(34) & ", " & Chr(34) & "To" & Chr(34) & ", " & Chr(34) & "Account" & Chr(34) & ", " & Chr(34) & "Amount" & Chr(34) & ", " & Chr(34) & "ctr2" & Chr(34) & ", " & Chr(34) & "Contract.2" & Chr(34) & ", " & Chr(34) & "Activity" & Chr(34) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Inserted Merged Column1" & Chr(34) & " " & Chr(61) & " Table.AddColumn(" & Chr(35) & "" & Chr(34) & "Reordered Columns3" & Chr(34) & ", " & Chr(34) & "Contract" & Chr(34) & ", each Text.Combine(" & Chr(123) & "" & Chr(91) & "ctr2" & Chr(93) & ", " & Chr(91) & "Contract.2" & Chr(93) & "" & Chr(125) & ", " & Chr(34) & "" & Chr(34) & "), type text), " & Chr(35) & "" & Chr(34) & "Removed Columns4" & Chr(34) & " " & Chr(61) & " Table.RemoveColumns(" & Chr(35) & "" & Chr(34) & "Inserted Merged Column1" & Chr(34) & "," & Chr(123) & "" & Chr(34) & "ctr2" & Chr(34) & ", " & Chr(34) & "Contract.2" & Chr(34) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Reordered Columns4" & Chr(34) & " " & Chr(61) & " Table.ReorderColumns(" & Chr(35) & "" & Chr(34) & "Removed Columns4" & Chr(34) & "," & Chr(123) & "" & Chr(34) & "Process Date" & Chr(34) & ", " & Chr(34) & "Invoice " & Chr(35) & "" & Chr(34) & ", " & Chr(34) & "From" & Chr(34) & ", " & Chr(34) & "To" & Chr(34) & ", " & Chr(34) & "Account" & Chr(34) & ", " & Chr(34) & "Amount" & Chr(34) & ", " & Chr(34) & "Contract" & Chr(34) & ", " & Chr(34) & "Activity" & Chr(34) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Replaced Value1" & Chr(34) & " " & Chr(61) & " Table.ReplaceValue(" & Chr(35) & "" & Chr(34) & "Reordered Columns4" & Chr(34) & "," & Chr(34) & "D0" & Chr(34) & "," & Chr(34) & "PRE-DES" & Chr(34) & ",Replacer.ReplaceText," & Chr(123) & "" & Chr(34) & "Activity" & Chr(34) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Replaced Value2" & Chr(34) & " " & Chr(61) & " Table.ReplaceValue(" & Chr(35) & "" & Chr(34) & "Replaced Value1" & Chr(34) & "," & Chr(34) & "D1" & Chr(34) & "," & Chr(34) & "DESIGN" & Chr(34) & ",Replacer.ReplaceText," & Chr(123) & "" & Chr(34) & "Activity" & Chr(34) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Replaced Value3" & Chr(34) & " " & Chr(61) & " Table.ReplaceValue(" & Chr(35) & "" & Chr(34) & "Replaced Value2" & Chr(34) & "," & Chr(34) & "D3" & Chr(34) & "," & Chr(34) & "PST-DES" & Chr(34) & ",Replacer.ReplaceText," & Chr(123) & "" & Chr(34) & "Activity" & Chr(34) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Replaced Value4" & Chr(34) & " " & Chr(61) & " Table.ReplaceValue(" & Chr(35) & "" & Chr(34) & "Replaced Value3" & Chr(34) & "," & Chr(34) & "R" & Chr(34) & "," & Chr(34) & "ROW" & Chr(34) & ",Replacer.ReplaceText," & Chr(123) & "" & Chr(34) & "Activity" & Chr(34) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Replaced Value5" & Chr(34) & " " & Chr(61) & " Table.ReplaceValue(" & Chr(35) & "" & Chr(34) & "Replaced Value4" & Chr(34) & "," & Chr(34) & "CS" & Chr(34) & "," & Chr(34) & "CEI" & Chr(34) & ",Replacer.ReplaceText," & Chr(123) & "" & Chr(34) & "Activity" & Chr(34) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Replaced Value6" & Chr(34) & " " & Chr(61) & " Table.ReplaceValue(" & Chr(35) & "" & Chr(34) & "Replaced Value5" & Chr(34) & "," & Chr(34) & "CC" & Chr(34) & "," & Chr(34) & "CONST" & Chr(34) & ",Replacer.ReplaceText," & Chr(123) & "" & Chr(34) & "Activity" & Chr(34) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Replaced Value7" & Chr(34) & " " & Chr(61) & " Table.ReplaceValue(" & Chr(35) & "" & Chr(34) & "Replaced Value6" & Chr(34) & "," & Chr(34) & "MT" & Chr(34) & "," & Chr(34) & "MATER" & Chr(34) & ",Replacer.ReplaceText," & Chr(123) & "" & Chr(34) & "Activity" & Chr(34) & "" & Chr(125) & "), " & Chr(35) & "" & Chr(34) & "Changed Type4" & Chr(34) & " " & Chr(61) & " Table.TransformColumnTypes(" & Chr(35) & "" & Chr(34) & "Replaced Value7" & Chr(34) & "," & Chr(123) & "" & Chr(123) & "" & Chr(34) & "Invoice " & Chr(35) & "" & Chr(34) & ", type text" & Chr(125) & "" & Chr(125) & ") in " & Chr(35) & "" & Chr(34) & "Changed Type4" & Chr(34) & "
Perhaps I am replacing too much?
Also, C192767TO15_D3 is the query and table name
Here is the attempt to pass through VBA:
Dim mFormula As String
mFormula = _
"*previous code*"
query1 = ActiveWorkbook.Queries.Add(Replace(tabName, "-", "") & tableCode, mFormula)
Any ideas on a simpler way to accomplish this task? If not, what is the best way to identify the issue?
Easiest thing:
Take your raw M code and put it somewhere accessible, here c:\temp\doit.txt
Write VBA that creates a powerquery (here query1) that reads in that file and uses it.
Based on https://blog.crossjoin.co.uk/2014/02/04/loading-power-query-m-code-from-text-files/ and https://learn.microsoft.com/en-us/office/vba/api/excel.queries.add
Sub CreatePowerquery()
Dim mFormula As String
mFormula = "let Source = Text.FromBinary(File.Contents(""C:\temp\doit.txt"")), EvaluatedExpression = Expression.Evaluate(Source, #shared) in EvaluatedExpression "
query1 = ActiveWorkbook.Queries.Add("query1", mFormula)
End Sub
Alternatively, store code in a range and use that