Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I just run into a problem and need your help.
Given is a query named Excelsources with this code:
let Source = Excel.Workbook(File.Contents("C:\Users\pfih\Desktop\Template.xlsx"), null, true) in Source
The purpose of the query is to fetch the source and to present all sheets available.
So every new query may use this one as a reference to the source (which solves the problem adjusting the source everytime a file has moved to another destination). So far so good.
The code in the new query Finance is:
let Source = Excelsources in Source
This works fine too.
After that I put the same code into a txt file named Finance.txt and replaced the original code of the query Finance with:
let Source = Text.FromBinary(File.Contents("C:\Users\pfih\Desktop\Finance.txt")), EvaluatedExpression = Expression.Evaluate(Source, #shared) in EvaluatedExpression
Everything still looks fine....
until I closed the query and got this message:
in english:
OLE DB or ODBC Error: [Expression.Error]. The name "Excelsource" is not available in this context
Does anybody seen this error before and solved it or has a solution for this?
Thx for your help
Pfister
Solved! Go to Solution.
Hmm is there a reason to decouple the dependencies by storing the query in a text file?
Anyways, the problem here is when the Desktop fills into the AS model, each query is evaluated separately, alone with their dependencies. Because in this case there isn't any apparent links between Finances and Excelsources, they are completely isolated. Hence when you pull #shared, you can't see Excelsources.
The easiest solution would be bringing back the dependency by adding a reference in the Finance query
let Source = Text.FromBinary(File.Contents("C:\Users\pfih\Desktop\Finance.txt")), EvaluatedExpression = Expression.Evaluate(Source, #shared), Reference = Excelsources in EvaluatedExpression
If you'd like to keep this setup and not introduce any hard links between the queries, then you will need to manually create a list of exports from #shared. You can use Record.Combine({#shared, MyExports}) to do this.
Hmm is there a reason to decouple the dependencies by storing the query in a text file?
Anyways, the problem here is when the Desktop fills into the AS model, each query is evaluated separately, alone with their dependencies. Because in this case there isn't any apparent links between Finances and Excelsources, they are completely isolated. Hence when you pull #shared, you can't see Excelsources.
The easiest solution would be bringing back the dependency by adding a reference in the Finance query
let Source = Text.FromBinary(File.Contents("C:\Users\pfih\Desktop\Finance.txt")), EvaluatedExpression = Expression.Evaluate(Source, #shared), Reference = Excelsources in EvaluatedExpression
If you'd like to keep this setup and not introduce any hard links between the queries, then you will need to manually create a list of exports from #shared. You can use Record.Combine({#shared, MyExports}) to do this.
hi pqian ,
why the error happen in Power BI Desktop and Excel Power Query is ok?
If I want to keep this setup and not introduce any hard links between the queries, where can I manually create a list of exports from #shared.
Reference = Record.Combine({#shared, MyExports}) is not work.
can you help?
Where can use Record.Combine({#shared, MyExports}) ?
Hi pqian
Your solution works. Thx for your advice.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |