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.

      • jmillsjmills's avatar
        jmillsjmills
        Helper III
        2 years ago

        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