I am using Get and Transform in Excel 2016 where each transform operation lists its M language code. Can that code be somehow used in VBA to automate several transformations within other VBA code?
Asked
Active
Viewed 1,598 times
0
-
related: [What is the M language for?](http://stackoverflow.com/questions/2891623/what-is-the-m-language-for) – Sep 17 '16 at 22:58
-
1It says in the stackoverflow blurb http://stackoverflow.com/questions/tagged/m that the language was incorporated into excel, so maybe a few google searches might uncover that interaction – Toby Allen Sep 18 '16 at 08:40
-
It is incorporated into Excel and is used with the Get & Transform functions. – Bob Sep 18 '16 at 17:08
-
2You can use VBA / VB.NET to automate the power query process. thisworkbook.queries is the collection. – James Heffer Sep 19 '16 at 08:52
-
@Jeeped - the M langauge is the coding language used to make Power Queries as far as I know... – James Heffer Sep 19 '16 at 08:55
1 Answers
0
Through the power of google I found the msdn refernce for M functions in excel.
https://msdn.microsoft.com/en-us/library/mt211003.aspx
It appears to be called Power Query now.
Toby Allen
- 10,997
- 11
- 73
- 124
-
Thanks. I had also used the "power of google" and found the link above but did not find the answer to my question. Can M be used within VBA code to perform transformations?? I am probably missing something obvious and would appreciate any guidance. – Bob Sep 18 '16 at 10:48
-
When you say transformations? Do you mean like updating the source of the query or adding in one or two steps? If so, then short answer is no. But the way you would update a queries formula, would be to retrieve the current formula, then to alter it to what you want, then to delete the query and add it again with the new formula as I think the formula property is read only and cannot be changed. But, to create a new query with a new formula is very possible, that's how I do it... – James Heffer Sep 19 '16 at 08:54