Power BI Data Service v Desktop Refresh

[jmillsjmills]

I find the PBI Service very frustrating with the privacy levels stuff. I had previously always set to ignore, until I learnt that you cannot refresh datasets in the service without having privacy levels set to organisational (and having "Combine Privacy Levels" set to on).

This then created issues whereby custom functions that I'd built separately and was referencing were then returning me Formula.Firewall errors. Accordingly, I have subsequently been wrapping up the custom functions to build them within the SAME query that then references them. This has meant my code gets pretty convoluted... But hey, it usually refreshes!

However, I have now got to the point where again although I have it refreshing fine in Desktop, I get this error refreshing it in Power BI Service:

[Unable to combine data] Section1/LatestGotFile/Changed Type1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.. The exception was raised by the IDbCommand interface. Table: LatestGotFile.

But I really cannot see anything externally referenced (i.e. there are no dependencies on other Power BI datasets). Any data is called DIRECTLY via the Web.Contents functions (or sometimes within the custom function, but that has still been fine to do before).

Can anyone see my convoluted code before and see what could be causing it? Sorry for the mess - it's mostly due to having to wrap everything up into one single query.

//REPLACE ISIN SEARCH
let
#"cstmFn" =
let
#"Table" = (symbol) as table =>
let
Source = Json.Document(Web.Contents("https://query2.finance.yahoo.com/v1/finance", [RelativePath="/search?q=" & symbol & "&lang=en-GB&region=GB&quotesCount=8&newsCount=0&enableFuzzyQuery=false&quotesQueryId=tss_match_phrase_query&multiQuoteQueryId=multi_quote_single_token_query&enableCb=false&enableNavLinks=true&enableEnhancedTrivialQuery=true&enableCulturalAssets=true&enableLogoUrl=true"])),

quotes = #table(1, {{Source[quotes]{0}[symbol]}}),
#"Converted to Table" = #table(1, {{quotes}})
in
#"Converted to Table"
in
#"Table",

//REPLACE MSTARDATAFINDER

#"morningstarDataFinder" =
let
#"Table2" = (symbol) as table =>

let
url = "/rest.svc/klr5zyak8x/security/screener?page=1&pageSize=10&sortOrder=Name%20asc&outputType=json&version=1&languageId=en-GB&currencyId=GBP&universeIds=&securityDataPoints=SecId%7CLegalName%7CName%7CIndustryName%7CSectorName%7CTenforeId%7CUniverse%7CExchangeId%7CTicker%7CClosePrice%7CMarketCap%7CDividendYield%7CPERatio%7CPEGRatio%7CMarketCountryName%7CEquityStyleBox%7CReturnD1%7CReturnW1%7CReturnM1%7CReturnM3%7CReturnM6%7CReturnM0%7CReturnM12%7CReturnM36%7CReturnM60%7CReturnM120%7CEBTMarginYear1%7CROEYear1%7CROICYear1%7CEPSGrowth3YYear1%7CRevenueGrowth3Y%7CDebtEquityRatio%7CNetMargin%7CROATTM%7CROETTM&filters=&term="&symbol&"&subUniverseId=",
token = "lstzFDEOhfFNMLikKa0am9mgEKLBl49T",

headers = [
#"accept" = "application/json",
#"Apikey"=token
],

body = "{""cid"": [""META""], ""pid"": [""150""],""sid"": 13001, ""p"": [""CY-2017"", ""CY-2018"", ""CY-2019"",""CY-2020"", ""CY-2021"", ""CY-2022"", ""CY-2023"",""CY-2024"", ""CY-2025"", ""CY-2026"", ""CY-2027""],""r"": ""20231021"", ""ciso"": ""USD""}",
body2 = Json.FromValue(Json.Document(body)),

source = Json.Document(Web.Contents("https://tools.morningstar.co.uk/api", [Headers=headers, RelativePath="" & url & ""])),
#"Converted to Table1" = Record.ToTable(source),
Value = #"Converted to Table1"{3}[Value],
#"Converted to Table" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"SecId", "LegalName", "Name", "IndustryName", "SectorName", "TenforeId", "Universe", "ExchangeId", "Ticker", "ClosePrice", "MarketCap", "DividendYield", "PERatio", "EquityStyleBox", "ReturnD1", "ReturnW1", "ReturnM1", "ReturnM3", "ReturnM6", "ReturnM0", "ReturnM12", "ReturnM36", "ReturnM60", "ReturnM120", "EBTMarginYear1", "ROEYear1", "ROICYear1", "EPSGrowth3YYear1", "RevenueGrowth3Y", "DebtEquityRatio", "NetMargin", "ROATTM", "ROETTM"}, {"SecId", "LegalName", "Name", "IndustryName", "SectorName", "TenforeId", "Universe", "ExchangeId", "Ticker", "ClosePrice", "MarketCap", "DividendYield", "PERatio", "EquityStyleBox", "ReturnD1", "ReturnW1", "ReturnM1", "ReturnM3", "ReturnM6", "ReturnM0", "ReturnM12", "ReturnM36", "ReturnM60", "ReturnM120", "EBTMarginYear1", "ROEYear1", "ROICYear1", "EPSGrowth3YYear1", "RevenueGrowth3Y", "DebtEquityRatio", "NetMargin", "ROATTM", "ROETTM"})
in
#"Expanded Column1"
in
#"Table2",

StepA = SharePoint.Files("https://goodhartpartnersllp383.sharepoint.com/sites/GlobalOpportunitiesTrust", [ApiVersion = 15]),
StepB = Table.SelectRows(StepA, each Text.Contains([Folder Path], "Data/CSVs")),
StepC = Table.SelectRows(StepB, each ([Extension] = ".CSV")),
StepD = Table.SelectRows(StepC, let StepD_latest = List.Max(StepC[Date modified]) in each [Date modified] = StepD_latest),
StepE = Table.AddColumn(StepD, "Url", each [Folder Path]&[Name]),
StepF = Table.SelectColumns(StepE,{"Url", "Name","Content"}),
StepG = Csv.Document(StepF[Content]{0},[Delimiter=",", Columns=33, Encoding=1252, QuoteStyle=QuoteStyle.None]),
StepH = Table.PromoteHeaders(StepG, [PromoteAllScalars=true]),
StepI = Table.TransformColumnTypes(StepH,{{"Accounting date", type date}, {"Portfolio ID", type text}, {"Portfolio Name", type text}, {"Asset ID", type text}, {"Holding", type number}, {"Clean Market Value Base", type number}, {"Accrued Interest Base", Int64.Type}, {"Reporting Currency", type text}, {"Market Value Local", type number}, {"Exchange Rate", type number}, {"Book Cost base", type number}, {"ISIN", type text}, {"CUSIP", type text}, {"SEDOL", type text}, {"Asset Description", type text}, {"Asset Type", type text}, {"Sub Asset Type", type text}, {"Account Name Fund manager", type text}, {"Account Number Fund manager", type text}, {"Maturity Date", type text}, {"Expiry Date", type text}, {"Trading Currency", type text}, {"Country Of Incorporation", type text}, {"Country Of Risk", type text}, {"Issuer", type text}, {"Industry Classification", type text}, {"Primary Exchange", type text}, {"Traded Exchange", type text}, {"Credit Rating Moodys", type text}, {"Credit Rating S&P", type text}, {"Credit Rating Fitch", type text}, {"Credit Rating Asset Manager", type text}, {"Outstanding Shares", Int64.Type}}),
StepJ = Table.AddColumn(StepI, "Weight", each [Clean Market Value Base]/List.Sum(StepI[Clean Market Value Base])),
StepK = Table.RemoveColumns(StepJ,{"Credit Rating Moodys", "Credit Rating S&P", "Credit Rating Fitch", "Credit Rating Asset Manager", "Account Name Fund manager", "Account Number Fund manager", "Maturity Date", "Expiry Date"}),
StepL = Table.AddColumn(StepK, "Custom", each if [Sub Asset Type] = "MUTUAL FUNDS" then "Mutual Funds" else if [Sub Asset Type] = "UNQUOTED EQUITIES" then "Unlisted Equities" else if [Asset Type] = "EQUITY" then "Equities" else "Cash"),
StepM = Table.SelectRows(StepL, each ([ISIN] <> "") and ([Custom] = "Equities")),
StepN = Csv.Document(Web.Contents("https://goodhartpartnersllp383.sharepoint.com/sites/GlobalOpportunitiesTrust/Shared%20Documents/Visi..."),[Delimiter=",", Columns=18, Encoding=1252, QuoteStyle=QuoteStyle.None]),
StepO = Table.ReplaceValue(StepN,"0",null,Replacer.ReplaceValue,{"Column18"}),
StepP = Table.PromoteHeaders(StepO, [PromoteAllScalars=true]),
StepQ = Table.TransformColumnTypes(StepP,{{"VACOMPANYID", Int64.Type}, {"VATICKER", type text}, {"COMPANYNAME", type text}, {"ISIN", type text}, {"CUSIP", type text}, {"BLOOMBERGTICKER", type text}, {"FIGICOMPANY", type text}, {"FIGIINSTRUMENT", type text}, {"FACTSETENTITYID", type text}, {"PRIMARYEXCHANGECODE", type text}, {"BASECURRENCY", type text}, {"VACOUNTRY", type text}, {"VAREGION", type text}, {"INDUSTRYID", Int64.Type}, {"INDUSTRYNAME", type text}, {"SECTORID", Int64.Type}, {"SECTORNAME", type text}}),
StepR = Table.NestedJoin(StepM, {"ISIN"}, StepQ, {"ISIN"}, "StepQ", JoinKind.LeftOuter),
StepS = Table.ExpandTableColumn(StepR, "StepQ", {"VACOMPANYID", "VACOUNTRY", "VAREGION", "INDUSTRYNAME", "SECTORNAME", "YAHOO"}, {"VACOMPANYID", "VACOUNTRY", "VAREGION", "INDUSTRYNAME", "SECTORNAME", "YAHOO"}),
StepT = Table.SelectRows(StepS, each ([YAHOO] = null)),
StepU = Table.AddColumn(StepT, "findNew", each cstmFn([ISIN])),
StepV = Table.ExpandTableColumn(StepU, "findNew", {"Column1"}, {"Column1"}),
StepW = Table.ExpandTableColumn(StepV, "Column1", {"Column1"}, {"Column1.1"}),
StepX = Table.RemoveColumns(StepW,{"YAHOO"}),
StepY = Table.RenameColumns(StepX,{{"Column1.1", "YAHOO"}}),
StepZ1 = StepS,
StepZ2 = Table.SelectRows(StepZ1, each ([YAHOO] <> null)),
StepZ3 = Table.Combine({StepZ2, StepY}),
StepZ4 = Table.SelectRows(StepL, each ([Custom] <> "Equities")),
StepZ5 = Table.Combine({StepZ3, StepZ4}),
StepZ6 = Table.SelectRows(StepZ5, each ([YAHOO] <> null)),
YAHOO_Result = StepZ6[YAHOO],
StepZ7 = Table.FromList(YAHOO_Result, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
StepZ8 = Table.AddColumn(StepZ7, "Custom", each "{""operator"":""EQ"",""operands"":[""ticker"","""&[Column1]&"""]}"),
StepZ9 = StepZ8[Custom],
StepZ10 = #table(1, {{StepZ9}}),
StepZ11 = Table.TransformColumns(StepZ10, {"Column1", each Text.Combine(List.Transform(_, Text.From), ","), type text})[Column1]{0},
headers = [
#"Cookie" = "GUCS=AUu5XkKY; A1=d=AQABBBLDQ2UCEPSEjYXESSQltkXWjKipLBIFEgABCAESRWVrZfbPb2UBAiAAAAcIDsNDZWF-EOU&S=AQAAAuFo7LWEMMH0QLTpeC2QLxA; GUC=AQABCAFlRRJla0IefQSB&s=AQAAAM78Zb8F&g=ZUPDHA; EuConsent=CP0nQEAP0nQEAAOACKENDdCgAAAAAAAAACiQAAAAAABhoAMAARBQEQAYAAiCgKgAwABEFAA; A1S=d=AQABBBLDQ2UCEPSEjYXESSQltkXWjKipLBIFEgABCAESRWVrZfbPb2UBAiAAAAcIDsNDZWF-EOU&S=AQAAAuFo7LWEMMH0QLTpeC2QLxA; A3=d=AQABBBLDQ2UCEPSEjYXESSQltkXWjKipLBIFEgABCAESRWVrZfbPb2UBAiAAAAcIDsNDZWF-EOU&S=AQAAAuFo7LWEMMH0QLTpeC2QLxA; cmp=t=1698939658&j=1&u=1---&v=101",
#"Content-Type" = "application/json"
// #"Authorization"="Bearer "& token
],
body = "{""offset"":0,""size"":100,""sortField"":""dayvolume"",""sortType"":""desc"",""quoteType"":""equity"",""query"":{""operator"":""or"",""operands"":[{""operator"":""or"",""operands"":["&StepZ11&"]}]}, ""userId"":"""",""userIdType"":""guid""}",
body2 = Json.FromValue(Json.Document(body)),
source = Json.Document(Web.Contents("https://query2.finance.yahoo.com", [Headers=headers, RelativePath="/v1/finance/screener?crumb=thB88nfrdYz&lang=en-GB&region=GB&formatted=true&corsDomain=uk.finance.yahoo.com",Content=body2])),
StepZ12 = Record.ToTable(source),
StepZ13 = Table.ExpandRecordColumn(StepZ12, "Value", {"result", "error"}, {"result", "error"}),
StepZ14 = Table.ExpandListColumn(StepZ13, "result"),
StepZ15 = Table.ExpandRecordColumn(StepZ14, "result", {"start", "count", "total", "quotes", "useRecords"}, {"start", "count", "total", "quotes", "useRecords"}),
StepZ16 = Table.ExpandListColumn(StepZ15, "quotes"),

#"Expanded quotes1" = Table.ExpandRecordColumn(StepZ16, "quotes", {"symbol", "twoHundredDayAverageChangePercent", "dividendDate", "fiftyTwoWeekLowChangePercent", "averageAnalystRating", "language", "dividendYield", "regularMarketDayRange", "earningsTimestampEnd", "epsForward", "regularMarketDayHigh", "twoHundredDayAverageChange", "twoHundredDayAverage", "askSize", "bookValue", "marketCap", "fiftyTwoWeekHighChange", "fiftyTwoWeekRange", "fiftyDayAverageChange", "firstTradeDateMilliseconds", "averageDailyVolume3Month", "exchangeDataDelayedBy", "dividendRate", "trailingAnnualDividendRate", "fiftyTwoWeekChangePercent", "fiftyTwoWeekLow", "regularMarketVolume", "market", "quoteSourceName", "messageBoardId", "priceHint", "regularMarketDayLow", "sourceInterval", "exchange", "region", "shortName", "fiftyDayAverageChangePercent", "fullExchangeName", "earningsTimestampStart", "financialCurrency", "displayName", "gmtOffSetMilliseconds", "regularMarketOpen", "regularMarketTime", "regularMarketChangePercent", "trailingAnnualDividendYield", "quoteType", "averageDailyVolume10Day", "fiftyTwoWeekLowChange", "fiftyTwoWeekHighChangePercent", "typeDisp", "trailingPE", "tradeable", "currency", "sharesOutstanding", "fiftyTwoWeekHigh", "regularMarketPreviousClose", "exchangeTimezoneName", "bidSize", "regularMarketChange", "priceEpsCurrentYear", "cryptoTradeable", "fiftyDayAverage", "epsCurrentYear", "exchangeTimezoneShortName", "regularMarketPrice", "marketState", "customPriceAlertConfidence", "forwardPE", "earningsTimestamp", "ask", "epsTrailingTwelveMonths", "bid", "priceToBook", "triggerable", "longName", "ipoExpectedDate", "prevName", "nameChangeDate"}, {"symbol", "twoHundredDayAverageChangePercent", "dividendDate", "fiftyTwoWeekLowChangePercent", "averageAnalystRating", "language", "dividendYield", "regularMarketDayRange", "earningsTimestampEnd", "epsForward", "regularMarketDayHigh", "twoHundredDayAverageChange", "twoHundredDayAverage", "askSize", "bookValue", "marketCap", "fiftyTwoWeekHighChange", "fiftyTwoWeekRange", "fiftyDayAverageChange", "firstTradeDateMilliseconds", "averageDailyVolume3Month", "exchangeDataDelayedBy", "dividendRate", "trailingAnnualDividendRate", "fiftyTwoWeekChangePercent", "fiftyTwoWeekLow", "regularMarketVolume", "market", "quoteSourceName", "messageBoardId", "priceHint", "regularMarketDayLow", "sourceInterval", "exchange", "region", "shortName", "fiftyDayAverageChangePercent", "fullExchangeName", "earningsTimestampStart", "financialCurrency", "displayName", "gmtOffSetMilliseconds", "regularMarketOpen", "regularMarketTime", "regularMarketChangePercent", "trailingAnnualDividendYield", "quoteType", "averageDailyVolume10Day", "fiftyTwoWeekLowChange", "fiftyTwoWeekHighChangePercent", "typeDisp", "trailingPE", "tradeable", "currency", "sharesOutstanding", "fiftyTwoWeekHigh", "regularMarketPreviousClose", "exchangeTimezoneName", "bidSize", "regularMarketChange", "priceEpsCurrentYear", "cryptoTradeable", "fiftyDayAverage", "epsCurrentYear", "exchangeTimezoneShortName", "regularMarketPrice", "marketState", "customPriceAlertConfidence", "forwardPE", "earningsTimestamp", "ask", "epsTrailingTwelveMonths", "bid", "priceToBook", "triggerable", "longName", "ipoExpectedDate", "prevName", "nameChangeDate"}),
#"Merged Queries1" = Table.NestedJoin(StepZ6, {"YAHOO"}, #"Expanded quotes1", {"symbol"}, "Expanded quotes1", JoinKind.LeftOuter),
#"Expanded Expanded quotes1" = Table.ExpandTableColumn(#"Merged Queries1", "Expanded quotes1", {"Name", "start", "count", "total", "twoHundredDayAverageChangePercent", "dividendDate", "fiftyTwoWeekLowChangePercent", "averageAnalystRating", "language", "dividendYield", "regularMarketDayRange", "earningsTimestampEnd", "epsForward", "regularMarketDayHigh", "twoHundredDayAverageChange", "twoHundredDayAverage", "askSize", "bookValue", "marketCap", "fiftyTwoWeekHighChange", "fiftyTwoWeekRange", "fiftyDayAverageChange", "firstTradeDateMilliseconds", "averageDailyVolume3Month", "exchangeDataDelayedBy", "dividendRate", "trailingAnnualDividendRate", "fiftyTwoWeekChangePercent", "fiftyTwoWeekLow", "regularMarketVolume", "market", "quoteSourceName", "messageBoardId", "priceHint", "regularMarketDayLow", "sourceInterval", "exchange", "region", "shortName", "fiftyDayAverageChangePercent", "fullExchangeName", "earningsTimestampStart", "financialCurrency", "displayName", "gmtOffSetMilliseconds", "regularMarketOpen", "regularMarketTime", "regularMarketChangePercent", "trailingAnnualDividendYield", "quoteType", "averageDailyVolume10Day", "fiftyTwoWeekLowChange", "fiftyTwoWeekHighChangePercent", "typeDisp", "trailingPE", "tradeable", "currency", "sharesOutstanding", "fiftyTwoWeekHigh", "regularMarketPreviousClose", "exchangeTimezoneName", "bidSize", "regularMarketChange", "priceEpsCurrentYear", "cryptoTradeable", "fiftyDayAverage", "epsCurrentYear", "exchangeTimezoneShortName", "regularMarketPrice", "marketState", "customPriceAlertConfidence", "forwardPE", "earningsTimestamp", "ask", "epsTrailingTwelveMonths", "bid", "priceToBook", "triggerable", "longName", "ipoExpectedDate", "prevName", "nameChangeDate", "useRecords", "error"}, {"Name", "start", "count", "total", "twoHundredDayAverageChangePercent", "dividendDate", "fiftyTwoWeekLowChangePercent", "averageAnalystRating", "language", "dividendYield", "regularMarketDayRange", "earningsTimestampEnd", "epsForward", "regularMarketDayHigh", "twoHundredDayAverageChange", "twoHundredDayAverage", "askSize", "bookValue", "marketCap", "fiftyTwoWeekHighChange", "fiftyTwoWeekRange", "fiftyDayAverageChange", "firstTradeDateMilliseconds", "averageDailyVolume3Month", "exchangeDataDelayedBy", "dividendRate", "trailingAnnualDividendRate", "fiftyTwoWeekChangePercent", "fiftyTwoWeekLow", "regularMarketVolume", "market", "quoteSourceName", "messageBoardId", "priceHint", "regularMarketDayLow", "sourceInterval", "exchange", "region", "shortName", "fiftyDayAverageChangePercent", "fullExchangeName", "earningsTimestampStart", "financialCurrency", "displayName", "gmtOffSetMilliseconds", "regularMarketOpen", "regularMarketTime", "regularMarketChangePercent", "trailingAnnualDividendYield", "quoteType", "averageDailyVolume10Day", "fiftyTwoWeekLowChange", "fiftyTwoWeekHighChangePercent", "typeDisp", "trailingPE", "tradeable", "currency", "sharesOutstanding", "fiftyTwoWeekHigh", "regularMarketPreviousClose", "exchangeTimezoneName", "bidSize", "regularMarketChange", "priceEpsCurrentYear", "cryptoTradeable", "fiftyDayAverage", "epsCurrentYear", "exchangeTimezoneShortName", "regularMarketPrice", "marketState", "customPriceAlertConfidence", "forwardPE", "earningsTimestamp", "ask", "epsTrailingTwelveMonths", "bid", "priceToBook", "triggerable", "longName", "ipoExpectedDate", "prevName", "nameChangeDate", "useRecords", "error"}),
#"Expanded fiftyTwoWeekHighChangePercent" = Table.ExpandRecordColumn(#"Expanded Expanded quotes1", "fiftyTwoWeekHighChangePercent", {"raw"}, {"fiftyTwoWeekHighChangePercent"}),
#"Expanded fiftyTwoWeekLowChangePercent" = Table.ExpandRecordColumn(#"Expanded fiftyTwoWeekHighChangePercent", "fiftyTwoWeekLowChangePercent", {"raw"}, {"fiftyTwoWeekLowChangePercent"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded fiftyTwoWeekLowChangePercent",{{"fiftyTwoWeekLowChangePercent", Percentage.Type}, {"fiftyTwoWeekHighChangePercent", Percentage.Type}})
in
#"Changed Type1"

[jmillsjmills]

HI @lbendlin - thanks so much for coming back! I really appreciate your help

Wherever I have needed to (including in other queries), I've amended what was...

StepN = Csv.Document(Web.Contents("https://goodhartpartnersllp383.sharepoint.com/sites/GlobalOpportunitiesTrust/Shared%20Documents/Visi..."),[Delimiter=",", Columns=18, Encoding=1252, QuoteStyle=QuoteStyle.None]),

to...

StepN = Csv.Document(Table.SelectRows(SharePoint.Files("https://goodhartpartnersllp383.sharepoint.com/sites/GlobalOpportunitiesTrust/", [ApiVersion = 15]), each ([Name] = "va_sec_master_20231027.csv")){[Name="va_sec_master_20231027.csv",#"Folder Path"="https://goodhartpartnersllp383.sharepoint.com/sites/GlobalOpportunitiesTrust/Shared Documents/Visible Alpha/"]}[Content],[Delimiter=",", Columns=18, Encoding=1252, QuoteStyle=QuoteStyle.None]),

So it works identically within the Desktop version. But publishing it to the Service I am still getting an error. Am I doing it wrong? Is there anything else you can see in the code? Because I think I have corrected for what you are describing generally.

My error is still:

[Unable to combine data] Section1/LatestGotFile/Changed Type1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.. The exception was raised by the IDbCommand interface. Table: LatestGotFile.

Thank you!!

[lbendlin]

StepN = Csv.Document(SharePoint.Files("https://goodhartpartnersllp383.sharepoint.com/sites/GlobalOpportunitiesTrust/", [ApiVersion = 15]){[Name="va_sec_master_20231027.csv",#"Folder Path"="https://goodhartpartnersllp383.sharepoint.com/sites/GlobalOpportunitiesTrust/Shared Documents/Visible Alpha/"]}[Content],[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.CSV]),

try this version.  Your error message comes from elsewhere - are you attempting to merge with another data source?

[jmillsjmills]

Same issue I'm afraid yes. I think the reason it doesn't like it is that StepA

StepA = SharePoint.Files("https://goodhartpartnersllp383.sharepoint.com/sites/GlobalOpportunitiesTrust", [ApiVersion = 15]),

Ultimately feeds the next calling of the data in "source":

source = Json.Document(Web.Contents("https://query2.finance.yahoo.com", [Headers=headers, RelativePath="/v1/finance/screener?crumb=thB88nfrdYz&lang=en-GB&region=GB&formatted=true&corsDomain=uk.finance.yahoo.com",Content=body2])),

because body2 is fed by StepZ11, which is fed by StepZ10 etc going all the way backwards to StepA

I've been looking into "Partionining" which would seem to be the answer.. But I just can't get my head around how to actually achieve it in this instance.

[lbendlin]

Yep. Your only chance ist to stuff everything into a single query. Calling one query from another won't work

Consider using a dataflow for the upstream query.

[Anonymous]

@lbendlin 

Hi, i just posted a related post regarding privacy level. just to make sure my understanding is correct- it is not possible to merge a cloud source (SharePoint) to a SQL source? i have a sharepoint query and a SQL query, then in the sharepoint query I perform the merge to the SQL query. it will not work in this way? 

[jmillsjmills]

It's not an SQL query. The key steps are basically:

- StepA uses = SharePoint.Files() to pull a full list of documents, then subsequent steps select the latest file in a folder...

- StepG opens the content from the CSV file, and ultimately pulls out a list of stock tickers

- StepN uses Csv.Document(Table.SelectRows(SharePoint.Files())) - no longer using Web.Contents() to open a static CSV file which converts (via a merge in StepR) the tickers from one format to another

- then source = Json.Document(Web.Contents()) to ultimately invoke an API with the headers that feature the tickers collected and converted in the steps above...

So I think the dependencies following on from one another causing the issue in the service? It all works fine in Desktop, just not service. I have all privacy levels set to Organisational.

[lbendlin]

Read the article again. It is rather hard to digest, but the gist is that you must put everything into the same partition.

Consider the dataflow.

[jmillsjmills]

Oops! Sorry misread you as the original chap!!

[jmillsjmills]

But that's what I am doing in the attached. Everything happens all in that code, and there are no separate Power Queries getting used. My issue is (I think) that one data source produces a result, that ultimately determines part of a next step that calls another data source... I'm not sure how to get round that part essentially.

Could I fold them into separate "partitions" within one query to make it work?

Behind the scenes of the Data Privacy Firewall - Power Query | Microsoft Learn

I thought that reading the above would get me there but it really hasn't.

I would really really appreciate any further help that you can please offer 

[lbendlin]

Don't use Web.Contents for your Sharepoint based data sources. Use SharePoint.Files instead.  The main idea is to keep the maintenance effort low and connect as high up in the folder tree as possible (essentially at the site level).

Yes, you need to keep your computations in the same Power Query partition.  No way around that.

Privacy levels have their purpose.  Only ignore them if you don't care about your data leaking.