cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
freelensia Regular Visitor
Regular Visitor

Make a shared query use another query as input (fix "may not directly access a data source" error)

Hi guys,

 

We know that when a query cannot simultaneously access an external data source (external files, excel tables, etc.) and another query from the power query list (power query editor).

 

Doing so will raise the "...may not directly access a data source" error.

 

However this limitation is ridiculous, especially for shared queries. By shared I mean queries that are stored in text files and called with code such as:

(TargetTable as table, TargetCol as text, LookupTable as table, LookupOldCol as text, LookupNewCol as text) =>
let
  GetFunction = Text.FromBinary(File.Contents(Excel.CurrentWorkbook(){[Name="PQMacrosFolder"]}[Content]{0}[Column1]&"GetCSVByDateAndName.txt")),
EvaluateFunction = Expression.Evaluate(GetFunction, #shared),
EvaluateFunction2 = EvaluateFunction(TargetTable, TargetCol, LookupTable, LookupOldCol, LookupNewCol)
in
    EvaluateFunction2

Shared queries by definition are referring to an external data source (the text file). So then how can I use a shared query that will act on another query? This need is very realistic. Imagine you wrote a PQ macro that modify a table (a query) in some way. Now you wanna store it in a central location to re-use (in the same way you store VBA macros in xlam file). However this is never possible with the current power query architecture.

 

Anybody has a work-around?

 

ImkeF @v-juanli-msft @Nolock @exmrsjones ?

3 REPLIES 3
PradipMCT Regular Visitor
Regular Visitor

Re: Make a shared query use another query as input (fix "may not directly access a data source&

Hi @freelensia,

 

As I understand your question you want all the queries to be stored in one central location so that it can be re-used multiple times?

 

According to me, there are two ways to do this. 

 

1. Custom function: you can create custom functions and then it can be re-used in other queries

2. Data Flows: this is like, Power Query on the cloud. Where anyone within the organisation can use this.

 

Hope that answers your question

 

Regards,

Pradip Microsoft Certified Trainer

Pradip's YouTube Channel

freelensia Regular Visitor
Regular Visitor

Re: Make a shared query use another query as input (fix "may not directly access a data source&

Thanks for the quick answer.

1. Yes I have created custom functions. However they can only be used in one Excel workbook, not multiple.

2. Data flows are not free, am I correct?

 

If so, these 2 suggestions do not solve my issue.

PradipMCT Regular Visitor
Regular Visitor

Re: Make a shared query use another query as input (fix "may not directly access a data source&

Hi @freelensia 

 

I though you're working in Power BI. Data flows only available in Power BI cloud. I am sorry i ran out of suggestions.

 

Regards,

Pradip Microsoft Certified Trainer

Pradip's YouTube Channel

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 304 members 2,496 guests
Please welcome our newest community members: