Loading M Code From Text Files fails with reference set

[Anonymous]

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

[pqian · Accepted 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.

[applezorro]

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?

[applezorro]

Where can use Record.Combine({#shared, MyExports}) ?

[Anonymous]

Hi pqian

Your solution works. Thx for your advice.