Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
monjerhossain
Frequent Visitor

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-

  1. 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.
  2. I can refresh updated data on Power BI Desktop but cannot in Power BI Service, it generates credential error. How to fix it?
  3. 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.
1 ACCEPTED SOLUTION
djnww
Impactful Individual
Impactful Individual

Hi @monjerhossain

 

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.

View solution in original post

6 REPLIES 6
djnww
Impactful Individual
Impactful Individual

Hi @monjerhossain

 

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
Impactful Individual
Impactful Individual

Hi @monjerhossain

 

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.

Dear @djnww,

 

Thank you so much for your suggestions. It is very helpful.

 

One more thing, my excel files contains raw data, table is not deifned. Is it ok if I open the excel data sources and define as a table for each? Would it chage anything it the visuals?

djnww
Impactful Individual
Impactful Individual

I have a question. Why is the data in Excel ? Is Excel used for data entry and you are using data that someone has manually entered or copied from somewhere ?

 

If it is raw data, then why isn't it just a CSV ?

 

I should correct myself. Use a Table if users are making changes to Excel like updating a user list or entering data regularly. If it is just raw data that you are copying from somewhere, then you don't need it to be in a Table.

Hi,

 

Few data is in CSV and few are in excel. Basically, we export some CSV from other system to be used in Dashboard but they are very few. Most of the data are extracted from other system or manually prepared for Dashboard in excel. These data will be replaced by the updated one, some are once in a week some are in a month.

 

Thanks.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.