Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@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' 😉
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.