Forum Discussion
Excel dataset periodic update on Power BI Desktop
Hi,
I have created some report on Power BI Desktop. My data sources are some excel and csv fiels on SharePoint and conencted through Web URL as becasue I cannot access SharePoint folder, I get "Access to the data source is forbidden" error. Web connection is working fine though. My data sources will be updated in every week and month. I have a lot of measures fields on Power BI for calculations. I have the following issues-
- If I just replace my excel data on SharePoint location, would it be updated on Power BI including the measures fields? My idea is- I will simply replace raw data on SharePoint excel sources and Power BI will calculate everyhting based on the updated data.
- I can refresh updated data on Power BI Desktop but cannot in Power BI Service, it generates credential error. How to fix it?
- My PBIX file is about 40 MB. is it OK for Power BI services for a smooth data visualization? or what maximum PBIX size can I used for Dashboard?
Thanks in advance for any suggenstion.
Question1:
I don't mean tabular format. The data needs to actually be defined as a Table in Excel. Take a look: http://www.contextures.com/xlExcelTable01.html
Question2:
Firstly, if you want to automate your refreshes online, then your data needs to sit in OneDrive Cloud. Power BI will refresh every 24 hours for the FREE version. It may refresh automatically with Sharepoint Online but I have never tested it. The other alternatives are to have the data sitting in a database. However, you will need Power BI PRO.
6 Replies
- djnww
Impactful Individual
Question1:
Yes, it will update, even the measure fields. However, you must ensure that the data in your Excel files are in an Excel Tables. This is currently how our company does it. We also use the Web method.
Quesion 2:
When you click on the 'publish' button in your Power BI Desktop, it will automatically load the files onto PowerBI Cloud. You must enter your organisational Email account and password in order for it to be published successfully. Perhaps you are referring to the scheduled refresh ?
Question 3:
40MB is fine to be published to Power BI Cloud. I don't know if there is a limit. We hae produced pbix files larger than 250MB before with no issues.
Good luck.
D
HI djnww,
Thanks for your prompt reply.
Question1:
Yes, our data is in tabular format. My idea is, I will copy the updated data and paste on the existing excel data source. I think it will work fine.
Question2:
I want to refresh dataset on Power BI Servcies, not in Desktop. I don't want to open Power BI Desktop any more once the visuliazation is finalized and published on Power BI Service. I will then just replace the data in the excel data source and refresh the data source in Power BI Service. In Power BI Service, there is no option to put organizaitonal credential to login for dataset refresh. Do you have suggestion on it?
Question3:
So, I can add more data to my PBIX file now. Thanks.
- djnww
Question1:
I don't mean tabular format. The data needs to actually be defined as a Table in Excel. Take a look: http://www.contextures.com/xlExcelTable01.html
Question2:
Firstly, if you want to automate your refreshes online, then your data needs to sit in OneDrive Cloud. Power BI will refresh every 24 hours for the FREE version. It may refresh automatically with Sharepoint Online but I have never tested it. The other alternatives are to have the data sitting in a database. However, you will need Power BI PRO.
