Dear Power BI Community,
I have the following online refresh error but the query works perfectly fine in desktop version. Can anyone help me diagnose what the problem is. I have changed data source settings and none of the options make a difference to the error. I have also used relative path in the custom function to construct the dynamic URL. Any other ideas why this is not working and what the error is?
ERROR MESSAGE
Underlying error code: -2147467259
Underlying error message: [Unable to combine data] Section1/200DMA Split Adjusted Values/Renamed Columns references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.MashupSecurityException.DataSources: [{"kind":"Web","path":"https://eodhistoricaldata.com/api/technical/EURUSD.FOREX?order=d\u0026fmt=json\u0026from=2005-12-28\u0026function=sma\u0026period=200\u0026splitadjusted_only=1\u0026api_token=HIDDEN"}]
Microsoft.Data.Mashup.MashupSecurityException.Reason: PrivacyError
Cluster URI: WABI-NORTH-EUROPE-redirect.analysis.windows.net
Activity ID: 926c9e96-2851-4544-a3bf-545e58fc06ba
Request ID: ee2c7625-df2a-7a79-da7c-8e09b13dca95
Time: 2022-01-21 07:17:26Z
QUERY
let
Source = #"Ticker Universe",
#"Removed Other Columns" = Table.SelectColumns(Source,{"EOD Stock Code"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each #"Fn Dynamic API EOD Price Split Adjusted"([EOD Stock Code])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"date", "open", "high", "low", "close", "adjusted_close", "volume"}, {"Custom.date", "Custom.open", "Custom.high", "Custom.low", "Custom.close", "Custom.adjusted_close", "Custom.volume"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom.date", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom.high", "High"}, {"Custom.low", "Low"}, {"Custom.close", "Close"}, {"Custom.adjusted_close", "Adjusted Close"}, {"Custom.volume", "Volume"}}),
#"Renamed Columns1" = Table.RenameColumns(#"Renamed Columns",{{"Custom.open", "Open"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type2",{{"Custom.date", "Date"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Adjusted Close", type number}, {"Volume", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type3", {"EOD Stock Code", "Date"}, #"20DMA Split Adjusted Values", {"EOD Stock Code", "Date"}, "20DMA Split Adjusted Values", JoinKind.LeftOuter),
#"Expanded 20DMA Split Adjusted Values" = Table.ExpandTableColumn(#"Merged Queries", "20DMA Split Adjusted Values", {"SMA 20 Day"}, {"20DMA Split Adjusted Values.SMA 20 Day"}),
#"Renamed Columns3" = Table.RenameColumns(#"Expanded 20DMA Split Adjusted Values",{{"20DMA Split Adjusted Values.SMA 20 Day", "20D MA"}}),
#"Merged Queries1" = Table.NestedJoin(#"Renamed Columns3", {"EOD Stock Code", "Date"}, #"50DMA Split Adjusted Values", {"EOD Stock Code", "Date"}, "50DMA Split Adjusted Values", JoinKind.LeftOuter),
#"Expanded 50DMA Split Adjusted Values" = Table.ExpandTableColumn(#"Merged Queries1", "50DMA Split Adjusted Values", {"SMA 50 Day"}, {"50DMA Split Adjusted Values.SMA 50 Day"}),
#"Renamed Columns4" = Table.RenameColumns(#"Expanded 50DMA Split Adjusted Values",{{"50DMA Split Adjusted Values.SMA 50 Day", "50D MA"}}),
#"Merged Queries2" = Table.NestedJoin(#"Renamed Columns4", {"EOD Stock Code", "Date"}, #"200DMA Split Adjusted Values", {"EOD Stock Code", "Date"}, "200DMA Split Adjusted Values", JoinKind.LeftOuter),
#"Expanded 200DMA Split Adjusted Values" = Table.ExpandTableColumn(#"Merged Queries2", "200DMA Split Adjusted Values", {"SMA 200 Day"}, {"200DMA Split Adjusted Values.SMA 200 Day"}),
#"Renamed Columns5" = Table.RenameColumns(#"Expanded 200DMA Split Adjusted Values",{{"200DMA Split Adjusted Values.SMA 200 Day", "200D MA"}}),
#"Merged Queries3" = Table.NestedJoin(#"Renamed Columns5", {"EOD Stock Code", "Date"}, #"RSI 14D Split Adjusted", {"EOD Stock Code", "Date"}, "RSI 14D Split Adjusted", JoinKind.LeftOuter),
#"Expanded RSI 14D Split Adjusted" = Table.ExpandTableColumn(#"Merged Queries3", "RSI 14D Split Adjusted", {"RSI 14D"}, {"RSI 14D Split Adjusted.RSI 14D"}),
#"Renamed Columns6" = Table.RenameColumns(#"Expanded RSI 14D Split Adjusted",{{"RSI 14D Split Adjusted.RSI 14D", "RSI 14D"}}),
#"Merged Queries4" = Table.NestedJoin(#"Renamed Columns6", {"EOD Stock Code", "Date"}, #"MACD Split Adjusted", {"EOD Stock Code", "Date"}, "MACD Split Adjusted", JoinKind.LeftOuter),
#"Expanded MACD Split Adjusted" = Table.ExpandTableColumn(#"Merged Queries4", "MACD Split Adjusted", {"MACD", "Signal", "Divergence"}, {"MACD", "Signal", "Divergence"}),
#"Sorted Rows2" = Table.Sort(#"Expanded MACD Split Adjusted",{{"Date", Order.Descending}})
in
#"Sorted Rows2"
CUSTOM FUNCTION
(Stock)=>
let
Source = Json.Document(Web.Contents("https://eodhistoricaldata.com/",
[RelativePath="api/technical/"&Stock&"?order=d&fmt=json&from=2005-12-28&function=sma&period=50&splitadjusted_only=1&api_token=HIDDEN"])),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"date", "sma"}, {"date", "sma"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"date", type date}, {"sma", type number}})
in
#"Changed Type"
Look forward to any assitance you can provide.
Thank you in advance.
