cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Olivecrona Regular Visitor
Regular Visitor

Issues refreshing from Excel Workbook OneDrive for Business

Hello, I have a problem, and found solutions that doesn't fit my problem.

 

Scenario is as follows:

1. Datasource is Excel Workbook (sheet, not table) that I store in my OneDrive for Business.

2. pbix-file on my computer has the Excel Workbook (1.) as datasource

3. PBI service report has Excel Workbook (1.) as dataset

4. I have followed all steps with the "web part" when connecting data via Excel from OneDrive for Business, and taken away =web? as well as changing authenitcation method to OAuth2 settings.

5. In PBI-service I even get postive response when I refresh, saying a refresh has been completed from my Excel Workbook from OneDrive for Business - the connection to the exel file seems allright

 

However... Niether my local pbix file or the Dataset on Service actually do the refresh - it says it does, but no value is changed, my data is all the same still, even when refresh has been done. I do not understand what is wrong.

I also noted that under "edit credentials" under Settings in Datasets o nService, I sometime needs to reset it, since it goes back to Anonymous from my OAuth2.

 

What to do?

 

//Sebastian

9 REPLIES 9
Olivecrona Regular Visitor
Regular Visitor

Refresh issues from Excel Workbook OneDrive for Business

Hello, I have a problem, and found solutions that doesn't fit my problem.

 

Scenario:

1. Datasource is Excel Workbook (sheet, not table) that I store in my OneDrive for Business.

2. pbix-file on my computer has the Excel Workbook (1.) as datasource

3. PBI service report has Excel Workbook (1.) as dataset

4. I have followed all steps with the "web part" when connecting data via Excel from OneDrive for Business, and taken away "=web?", authenitcation method to OAuth2 settings and so on.

5. In PBI-service I even get postive response when I refresh, saying a refresh has been completed from my Excel Workbook from OneDrive for Business.

 

However... Niether my local pbix file and report on Service actually does the refresh - my data is all the same still, even when refresh has been done. I do not understand what is wrong.

I also noted that under "edit credentials" under Settings in Datasets, I sometime needs to reset it, since it goes back to Anonymous from my OAuth2.

 

What to do?

 

//Sebastian

Moderator v-yuezhe-msft
Moderator

Re: Issues refreshing from Excel Workbook OneDrive for Business

@Olivecrona,

How do you create the dataset in Power BI Service? Directly publish PBIX file from Power BI Desktop to Power BI Service, or connect to the PBIX file in Power BI service using Get Data->Files->OneDrive for business option?

And do you use OneDrive refresh option or schedule refresh option in Power BI Service? What changes do you make in eXCEL File? When data refresh in Service, the refresh will not reflect in PBIX file, you would need to open the PBIX file and click Refresh button in the PBIX file.

In addition, in Power BI Service, you don't need to reset the credential as it is a expected behaiover that it changes to Anonymous from OAuth2.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Olivecrona Regular Visitor
Regular Visitor

Re: Issues refreshing from Excel Workbook OneDrive for Business

Excel OneDrive.PNG

Hello @v-yuezhe-msft and thank you for for answer.

Service:

My dataset is an Excel file on OneDrive for business which I have established a connection to.
I am not using Get data->Files->OneDrive; Instead I am using the URL for setting my credentials, the connection is said to already be established since I'm working via Office365. I have not recieved any errors or anything.

Regarding OneDrive refresh, that option is not available to me in Service.

 

Power BI Desktop:

Get data->Web->"proper way" to link my Excel on OneDrive for Business.

 

Neither Service or Desktop refreshes when I update my data Excel on OneDrive. (Refreshed is said by both to be completed, but no new data has been loaded)

 

Best Regards

/Sebastian

 

 

Moderator v-yuezhe-msft
Moderator

Re: Issues refreshing from Excel Workbook OneDrive for Business

@Olivecrona,

I am confused about the process that you connect to Excel file in Power BI Service. To connect to Excel in Power BI Service, you should use Get Data-> Files-> OneDrive for bussiness option and then set refresh for the dataset under Settings of Power BI Service.
1.PNG

In addition, how do you make changes to the Excel on Onedrive? Do you delete the Excel and re-upload Excel to OneDrive, or open the Excel in Excel online and make changes? Do you add new columns or add/update new rows in the Excel?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Olivecrona Regular Visitor
Regular Visitor

Re: Issues refreshing from Excel Workbook OneDrive for Business

Hello!

No, originally, I published my Desktop report to Service.

Since my source/dataset is Excel on OneDrive for business, a connection was established and I didn't need to apply a gateway.

And since I now have the report on Service, where the dataset is Excel on OneDrive (which the connection established), I thought the correct way now to update the data in both Service report and Desktop report would be to refresh dataset and report.

How I am doing that is simply by adding the new rows of data for last month in my excel sheet

 

 

 

/

SebastiaSkärmklipp.PNGn

 

Olivecrona Regular Visitor
Regular Visitor

Re: Issues refreshing from Excel Workbook OneDrive for Business

And just to clarify:

I am not interested in (and I am aware of) that the report on service is now its own report. I am not looking for changes in the service report regarding changes in vizualisations, or measures, or columns made in desktop.

 

the only thing I am interested in is to be able to have Excel in OneDrive for business as my source/dataset for Desktop and for Service. As of now, none of them refreshes the correct data (no data at all)  even though a connection is established.

 

Best regards/

Sebastian

Moderator v-yuezhe-msft
Moderator

Re: Issues refreshing from Excel Workbook OneDrive for Business

@Olivecrona,

In Query Editor of Power BI Desktop, click Refresh  Preview and check if new rows are added to the imported table.

In Power BI Service, check refresh history  to see if  there are any error message.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Olivecrona Regular Visitor
Regular Visitor

Re: Issues refreshing from Excel Workbook OneDrive for Business

@v-yuezhe-msft 

I've done both of those things

 

In desktop, no rows are added

 

In Service, no rows are added although refresh has been done and refresh history has correct datetime for latest refreshes

 

This is a problem I can't get my head around

 

//Sebastian

Moderator v-yuezhe-msft
Moderator

Re: Issues refreshing from Excel Workbook OneDrive for Business

@Olivecrona,

It is strange. Could you please connect to the Excel in a new PBIX file, and check if the new rows of the Excel file appear in PBIX file?

Also you can share me the Excel file, I will test it in my Desktop. 

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 157 members 1,972 guests
Please welcome our newest community members: