Refresh error: rebuild connection with custom function accessing data source

[JuanSombrero]

Hello everybody,

After alot of hours searching, I admit it, I need some help in order not to have a nervous breakdown 😉

I have the following situation at hand:

1) A list of tickers (stock symbols) loaded from an Excel file in my personal OneDrive

2) A custom function looking up historical stock data from Yahoo Finance

All works fine in desktop, as long as I ingore privacy levels. When I do not ignore them, I get a formula.firewall error (pretty much like I am experiencing in the service after publishing). When I publish and I try to refresh the data, I get the following error:

[Unable to combine data] Section1/Portfolio/Expanded GetData references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

The final query is as follows:

let
Source = Connection_Transactions,
#"Filtered Rows" = Table.SelectRows(Source, each ([Type] = "Buy" or [Type] = "Deposit")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Stock"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Stock", Order.Ascending}}),
#"Invoked Custom Function" = Table.AddColumn(#"Sorted Rows", "GetData", each fn_Yahoo_Finance_Data([Stock])),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"GetData"}),
#"Expanded GetData" = Table.ExpandTableColumn(#"Removed Errors", "GetData", {"Date", "Rate"}, {"Date", "Price"})
in
#"Expanded GetData"

where the source refers to another query that simply makes the connection to the OneDrive Excel file

The fn_Yahoo_Finance_Data custom function has the following query

let
Source = (StockSymbol as text) => let
Source = Json.Document(Web.Contents("https://query1.finance.yahoo.com/v8/finance/chart/", [RelativePath = StockSymbol & "?range=5y&interval=1d"])),
chart = Source[chart],
result = chart[result],
result1 = result{0},
StartBranch = result1,
timestamp = StartBranch[timestamp],
#"Converted to Table" = Table.FromList(timestamp, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Date", each 25569 + ( [Column1]/60/60/24 )),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
EndBranchDate = Table.RemoveColumns(#"Added Index",{"Column1"}),
Custom1 = StartBranch,
indicators = Custom1[indicators],
adjclose = indicators[adjclose],
adjclose1 = adjclose{0},
adjclose2 = adjclose1[adjclose],
#"Converted to Table1" = Table.FromList(adjclose2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
EndBranchPrice = Table.AddIndexColumn(#"Converted to Table1", "Index", 0, 1),
#"Merged Queries" = Table.NestedJoin(EndBranchPrice, {"Index"}, EndBranchDate, {"Index"}, "EndBranchPrice", JoinKind.LeftOuter),
#"Expanded EndBranchPrice" = Table.ExpandTableColumn(#"Merged Queries", "EndBranchPrice", {"Date"}, {"Date"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded EndBranchPrice",{"Index"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Price"}}),
#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each [Date] >= #date(2019, 1, 1)),
#"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows1",{{"Price", "Rate"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "Ticker", each StockSymbol),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom1",{"Ticker", "Date", "Rate"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Ticker", type text}})
in
#"Changed Type2"
in
Source

This function is repeated for every row in the Excel file, calling for the historical stock data. I use a personal gateway for this.

I am aware about the ins and outs of data privacy level checks, and have found numerous articles from ao Chris Webb. I know you can 'stage' queries as a workaround, BUT...

as my custom function actually calls for the second data source, I don't think I can apply such a solution.

I think there are two possible solutions here, but I am not sure how to apply any of them:

1) find a way to stage queries for this particular case, or tweak my queries one way or the other as a workaround

2) change some data privacy settings in the online service

But again, unfortunately, I tried everything I could think of, without result.

Any help would be appreciated alot, and I do mean ALOT 😉

regards,

Jan

[JuanSombrero]

@cwebb I hate to call upon you directly, but I keep running into your articles and videos about refresh errors and formula.firewall issues. Am I right saying that my case is different, or am I missing out on something? Again, sorry to call you out like this, but you seem to be "the" specialist on this matter. And I find this issue more than frustrating, being the kind of person that can loose quite some sleep when 'things should work but they don't' 😉

[GilbertQ]

Hi @JuanSombrero 

Have a look at this blog post by Ken Puls which should find a way for you to get this working and get around the multiple sources

Power Query Errors: Please Rebuild This Data CombinationThe Excelguru Blog

[JuanSombrero]

Hi @GilbertQ 

I was aware of that article but I am not sure how to apply this to my particular case here. I have separated the OneDrive dataconnection into a separate query (hence my first step "Source = Connection_Transactions").

I might be missing out on something, but how would you apply this to my example? As my second data source is called upon by my custom M function (fn_Yahoo_Finance_Data) I don't see how to 'stage' this thing, or how to apply the articles content to this example.

Am I missing something here?

thx,

Jan