Cannot Connect to Business One Drive files

[knotpc]

I am developing a financial dashboard using Power BI desktop and then publishing it to https://app.powerbi.com. The dashboard is then shared with executives in the company. During the development, two limitations with Live Data connections emerged that forced me to use the import data mode instead. The first was needing a common calendar to handle dates across Syspro DB tables. The second issue was needing to add a Financial Baseline that contains P&L data that Syspro does not contain.

I now have the following data types used in my Power Bi Desktop that are then published to apps.powerbi.com:

1. Syspro database, a Gateway is configured for access in apps.powerbi.com. 
2. A Calendar Table that was created within Power Bi Desktop.  
3. A Financial Baseline.XLSX is stored on OneDrive for Business. The data is used for forecasting and creating P&L data, it is not possible to include this type of data in Syspro. 

The first issue I have run into using this method is related to OneDrive for Business. When I select WEB as the file type, I then enter the URL LINK. I choose Organizational Login. I am then prompted for my username and password. I enter those, press submit and get this error "The credentials provided cannot be used for the Web source. Please update the credential type through a refresh or in the Data Source Settings dialog to continue."  I tested the link using a Chrome. During the authintication process, I am required to have my phone receive an SMS code that I then must enter to access the file (I love all the security). Obviously Power BI Desktop cannot handle this added step.   

The above issue forced me to use Power BI Desktop's Get Data from FILE function for the Financial Baseline ONLY. The Syspro database is still configured to use the GATEWAY once published. However, once published to apps.powerbi.com the ability to REFRESH the data no longer works as the "Scheduled Refresh" area turns greyed out. If I remove the Financial Baseline.XLSX and republish to apps.powerbi.com the "Scheduled Refresh" works again. 

As another workaround attempt, I went to https;//apps.powerbi.com/groups/me/list/datasets and clicked CREATE. Then chose to Create new Content - Files and choose One Drive for Business.  Using this method I was able to select the Financial Baseline.XLSX and it was successfully imported into https://apps.powerbi.com/groups/me/list/datasets. I then went back to Power Bi desktop to add the file to my project. Get-Data - Power Bi Dataset. I then get the message " The Connect Live option for this file is disabled....". Basically, the error is telling me I cannot mix Live Data with Import. :(.   

At the end of the day, I need to have the dashboard refresh. Which means I need app.powerbi.com to be able to "REFRESH" both the Syspro DB and the Baseline Financials.XLSX. As I am unable to connect One Drive for business files I am stuck. I am open to ANY suggestions that are SECURE. 

Thanks as always for the communities help. 

[v-yuta-msft]

Hi knotpc,

Please check if you have followed the steps below:

• Navigate to your OneDrive for Business location using a browser. Right-click the file you want to use, and select Open in Excel.
• In Excel, select File > Info and select the link above the Protect Workbook button. Select Copy link to clipboard (your version might say Copy path to clipboard).
• In Power BI Desktop, select Get Data > Web.
• Paste the link into the From Web dialog (do not select OK yet).
• Notice the ?web=1 string at the end of the link - you must remove that portion of the Web URL string before selecting OK, in order for Power BI Desktop to properly navigate to your file.
• If Power BI Desktop prompts you for credentials, choose either Windows (for on-premises SharePoint sites) or Organizational Account (for Office 365 or OneDrive for Business sites).

Regards,

Jimmy Tao

[Anonymous]

Thanks for the details about opening in Excel that provided the right URL to use.