Forum Discussion

t-jones's avatar
t-jones
Frequent Visitor
6 years ago

Power BI Dataset not refreshing on Services

Hi All,

I have fetched the Salesforce reports and created tables for each report and applied merger queries to combine all data into a single table "Contact Evaluation", then I created a reference table of this and updated the query of reference table (i.e. Export CSV) so as to trigger an MS flow which takes the data of source table and exports it to CSV which is later saved in SharePoint Online library by the same flow. I have tested all the functionality and manually refreshed the data which was working fine on Power Bi Desktop and then I published the report to Power BI Services.

 

I have scheduled few refreshes on Services but they are not working. Each time the schedule is run it fails and gives the below error: 

[Unable to combine data] Section1/Export CSV/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Table: Export CSV.

 

Below is the query of Export CSV from which flow is triggered using an HTTP request.

 

let
Source = #"Contacts Evaluation",
ToArray= Table.ToRecords(Source),
ToJsonBinary = Json.FromValue(ToArray),
WebCall = Web.Contents("https://prod-50.westus.logic.azure.com:443/workflows/7b4fe445b3fb4e65a03e0a2de4ad6cb8/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=bLUyFTAeH74PmeEJ_5QDer17_QiEF9wL4yASV26yVFQ", [Content =ToJsonBinary]),
LinesFromBinary = Lines.FromBinary(WebCall)
in
LinesFromBinary

 

Under Data Source Credentials Salesforce and SharePoint credentials are working fine and even I can update it whenever needed, but in the web, which is basically the same URL which is triggering the flow is having "Anonymous" with Privacy level "None" and skip test checkbox is checked is showing an error when I am trying to sign in without skip test checkbox checked and showing below error:

Failed to update data source credentials: "Web.Contents" failed to get contents from 'https://prod-50.westus.logic.azure.com/workflows/7b4fe445b3fb4e65a03e0a2de4ad6cb8/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=bLUyFTAeH74PmeEJ_5QDer17_QiEF9wL4yASV26yVFQ' (400): Bad

 

I am not sure what actually causing the issue, I only need the schedule refresh to work properly on Services as well as the refreshes are working on PBI Desktop and for that, I have listed all the issues which somewhat look relevant to me. Hope my question makes sense. Please suggest some way so I can get this issue fixed.

 

Any help would be appreciated.

 

Thanks in advance

 

Need Help
Tips and Tricks

3 Replies

  • ReportGuru's avatar
    ReportGuru
    Icon for Post Patron rankPost Patron
    2 years ago

    Hi t-jones were you able to find a solution? I've used the Salesforce connector but I had some issues with my data too. As a workaround, maybe you can try to test your connection with a 3rd party connector, I've never had any issue when updating my report in service. I've tried windsor.ai, supermetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Salesforce connector in the data sources list:

     

     

    After that, just grant access to your Salesforce account using your credentials, then on preview and destination page you will see a preview of your Salesforce fields:

     

     

    There just select the fields you need. It is also compatible with custom fields and custom objects, so you'll be able to export them through windsor.  Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url. 

     

  • metrica's avatar
    metrica
    Icon for Post Prodigy rankPost Prodigy
    2 months ago

    Hi t-jones ,

     

    I would not treat this as a Salesforce credentials issue. The error points to Power Query’s data privacy firewall.

     

    Your Export CSV query references Contacts Evaluation, then uses Web.Contents() to call an external Flow or Logic Apps HTTP endpoint. In Power BI Service, that means the refresh is trying to take data from one source and send it to another source during query evaluation, which the firewall can block.

     

    I would avoid using Power BI scheduled refresh as the step that triggers the CSV export. A cleaner design would be:

    1. Refresh Salesforce data separately.

    2. Store or model the refreshed data in Power BI, a dataflow, Fabric, or another staging layer.

    3. Run the CSV export to SharePoint as a separate Power Automate, Logic Apps, Fabric pipeline, or scheduled job.

     

    Also, since this starts from Salesforce Reports, keep in mind that the native Salesforce Reports connector has the frustrating 2,000-row limit.

     

    If you want to simplify the Salesforce extraction part, Power BI Connector for Salesforce by Metrica Software can help:
    https://appexchange.salesforce.com/appxListingDetail?listingId=31526f0e-abd8-4cb5-bd1a-3bd56b5c0577

    It lets you prepare Salesforce data sources inside Salesforce, select the needed objects and fields, apply filters, preview the output, and connect Power BI to a cleaner prepared source.

     

    This will not fix the Power Query firewall issue by itself, but it can make the Salesforce refresh part of the pipeline cleaner and easier to separate from the CSV export automation.

     

    Docs and support:
    https://metricasoftware.com/docs/salesforce/
    https://metricasoftware.com/docs/salesforce/contact-support/

     

    Happy to show how we would split this into a clean Salesforce refresh step and a separate CSV export flow.

     

    Cheers,
    Metrica Team