Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Loading M Code From Text Files fails with reference set

 

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 

Unbenannt.PNG

Everything still looks fine....

until I closed the query and got this message:

Unbenannt2.PNG

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

 

1 ACCEPTED SOLUTION
pqian
Employee
Employee

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.

View solution in original post

4 REPLIES 4
pqian
Employee
Employee

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}) ?

Anonymous
Not applicable

Hi pqian

Your solution works. Thx for your advice.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.