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:
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.
Please check if you have followed the steps below: