Copy power query from a worbook, execute it and load its result into another workbook (with vba)

huangapple go评论61阅读模式
英文:

Copy power query from a worbook, execute it and load its result into another workbook (with vba)

问题

我有一个在工作簿中的一组带参数的查询,我想将这些查询执行到其他工作簿中。

其中一个查询示例是Fxquery(Tab as Table),而Tab将是每个工作簿的第一个工作表上的表格。

实际上,我可以从原始文件中获取所有查询,然后遍历我想要应用修改的所有工作簿。但是我不知道如何执行这些查询,而不只是复制M代码。

谢谢。
(如果有任何错误,请原谅,我的英语不是很好)

英文:

I got a group of parameterized queries in a workbook and i want to execute these queries into others workbooks.

One of these queries is for exemple Fxquery(Tab as Table) and Tab would be a table on the first sheet of each workbook.

Actually a can get all the queries from the origine file and go throught all the workbook i want to apply the modifications. But i do not know how to execute the queries withtout just copying the m code.

Thank you.
(Excuse me if there is any fault i'm not that good in english)

答案1

得分: 1

你无法在没有M代码的情况下执行文件中的M代码。您可以编写一个VBA宏,打开每个文件并动态创建您想要的M代码(将问题标记为VBA)。或者,事先设置每个文件,使其具有读取共享M代码并可执行的查询:

// 从文本文件加载共享的M代码
let Source = Text.FromBinary(File.Contents("C:\PowerQueryQueries\DynamicQuery.txt")),
EvaluatedExpression = Expression.Evaluate(Source, #shared)
in EvaluatedExpression

英文:

You can not execute M code in a file without having the M code in there. You could write a VBA that opens each file and dynamically creates the M code you want in each file (tag a question to VBA ). Or perhaps, set up each file in advance to have a query that reads in shared M code that can be executed:

//Load shared M code from text file
let  Source = Text.FromBinary(File.Contents("C:\PowerQueryQueries\DynamicQuery.txt")),
EvaluatedExpression = Expression.Evaluate(Source, #shared)
in EvaluatedExpression

huangapple
  • 本文由 发表于 2023年5月25日 17:00:00
  • 转载请务必保留本文链接:https://go.coder-hub.com/76330530.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定