Dashboard automation

[acash993]

Hi All,

I have created a dashboard, Excel as a source and now I want to automate it.

We receive the report (Excel) every Monday so is there any way we can automate this process such that Dashboard get refreshed based on the latest report?

[croberts21]

(Sorry, this site messes up my bullet points when I post the reply.)

Hi, I did the same thing. Here's the summary of how we handled it via putting the Excel file on Sharepoint. The detailed instructions would run at least 3-4 printed pages. 

1. Put the spreadsheet on a Sharepoint site. Getting this to work is very complicated, as there are many instructions and videos on the internet that just don't work. When you update the Excel file use the exact same filename or PBI will not recognize it. Do not change the case of any letters in the Excel filename or it still may not work.
2. Make your PBI file which links to the Excel spreadsheet on the Sharepoint site. You can also use Onedrive I think. 
3. Test the PBI report by updating the Excel file on Sharepoint. Make sure the file is saved then in PBI do a Refresh. 
4. Now Publish your PBIX file to the PBI service/website.
5. Go to the PBI service at https://app.powerbi.com.
6. Set up auto refresh. This requires several steps. For general info: https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data
   1. I believe you need admin permissions on app.powerbi.com to do the next 2 steps setting up a gateway.
   2. Set up a on-premises data gateway. You must download and install the gateway on the computer where PBI Desktop resides I believe. I don't know for sure as I didn't do this part. For more info: https://powerbi.microsoft.com/en-us/blog/on-premises-data-gateway-may-2022-update-is-now-available/
   3. Set up credentials for all datasets other than the Excel Sharepoint file. (We had one ODBC data source plus the Excel file for this report.)
   4. Now set up the auto refresh to be every N days. 

REALLY critical points to consider:

1. We made a Sharepoint site that is public to all people in the company that will use the spreadsheet. They edit the spreadsheet directly on the Sharepoint site with the following caveats.
2. The column headers in the Excel spreadsheet must be exactly the same in the spreadsheet every single time or the PBI refresh process will fail. Even adding or deleting a space will make the PBI refresh process fail. And I found there was no way to protect the first row (where the spreadsheet headers are) in Excel without causing more PBI headaches.
3. The columns must also contain the exact same type of data for each new update to the spreadsheet file. You cannot add or delete any columns once this is set up in PBI or you will need to re-add the Excel file back into PBI. Delete the datasource for the Excel file, now add it back in. This can get quite tedious.

Other points

1. We used our Microsoft/Office 365 credentials entered into PBI for the spreadsheet file. 
2. Sometimes you need to sign out and sign back into PBI to test the refresh of the spreadsheet in PBI Desktop. 
3. I am unsure of the details so I'm unable to give them to you as I did not do some parts, someone else did.
4. You must use the PBI Sharepoint Folder connector to make this work. Thus when you enter the URL of the spreadsheet on Sharepoint, you do not enter the filename, you enter the folder that contains the file. PBI will ask you for some steps to get the exact filename and spreadsheet tab after this.