Forum Discussion

jmillsjmills's avatar
jmillsjmills
Helper III
2 years ago

Power BI Data Service v Desktop Refresh

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 lev...
Need Help
jmillsjmills's avatar
jmillsjmills
Helper III
2 years ago

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's avatar
    lbendlin
    Super User
    2 years ago
    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's avatar
      jmillsjmills
      Helper III
      2 years ago

      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's avatar
        lbendlin
        Super User
        2 years ago

        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.