Portland Power Platform User Group

Please login or click SIGN UP FOR FREE to create your PowerPlatformUG account to join this user group.

Power BI Service refresh issue due to (misconfigure credential) credential authentication failure

  • 1.  Power BI Service refresh issue due to (misconfigure credential) credential authentication failure

    Posted Oct 29, 2020 06:02 PM
    Edited by Mark Wheeler Oct 30, 2020 12:55 PM
    Hello Power BI Community,

    I am having a Power BI Service dataset refresh failure.

    I receive the following summary error: Data source error<pii>The credentials provided for the Web source are invalid. ...

    I am loading in an Excel file that sits on a SharePoint site via Source = Excel.Workbook(Web.Contents("https:// ...

    Under a Power BI Desktop configuration, using an Organizational Account (which is displayed), the refresh is successful.

    When the PBIX file is pushed to Power BI Service and a refresh is triggered, the refresh fails.

    When investigating credential configuration for the web connector, the service only displays/allows the Anonymous or Basic Authentication method.

    My assumption is that because the Power BI Service is not using the PBIX file's Organizational Account config, the refresh fails.

    Suggestions?

    Best regards,
    Mark

    ====== Update (30-Oct-20 - Resolved) =====
    Background: Power BI report using Excel loaded data had been working for many months with a daily refresh successfully running.
    Earlier in the week the source location of an excel file changed which caused daily refresh to fail. When the location of the file was updated within the PBIX file and a Desktop refresh was run the dataset was properly updated. Once updated PBIX file was pushed to the Power BI Service dataset refresh failed.
    The issue seemed to be the Web connector (SharePoint) functionality. Authentication on Power BI Service seems to work differently than on the Power BI Desktop version (or expect something different when authenticating). A subtle difference in the location string of the web connector changes the authentication options presented when views from the Power BI Service (Organizational option was not available as seen in Desktop version). As such Power BI Service could not apply the appropriate authentication credentials whereas Power BI Desktop could.

    Solution: An Excel generated path structure entered into the Power Query (M) - Source entry enabled Power BI Service to authenticate correctly.

    Here is the original string used (this entry was modified from original entry by hand). The Excel load worked on Desktop refresh but failed to authenticate on Power BI Service.

    https://xxx.xxx.com/:x:/r/sites/collaboration2/xxx-xx-xxx/Documents/R%26D%20xxx%20Leaders%20Files/xxx%20Process%20Managers/xxx_R%26T_List.xlsx

    Here is the working string used in the ultimate solution (Excel string captured via process outlined in video link below).

    https://xxx.xxx.com/sites/collaboration2/xxx-xx-xxx/Documents/R&D%20xxx%20Leaders%20Files/xxx%20Process%20Managers/xxx_R&T_List.xlsx

    Video method: How to connect to specific file from SharePoint in Power BI

    https://www.youtube.com/watch?v=EDy4bCIov_s