cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ankur_raj Frequent Visitor
Frequent Visitor

Issue with refreshing .pbix file to power bi service

I have created a MS Flow where when an email is received, it creates an entry in an excel file stored on OneDrive for Business. I am trying to create a dashboard which shows average time of receiving specific emails. It seems MS Flow doesn;t support the excel files which have calculated columns in them. And Power BI service doesn't support any kind of calculated columns, So I created the report using Power BI desktop and the .pbix file is stored on the OneDrive for Business.  I am running into issues with refreshing the .pbix file on Power BI service where it says I need a Power BI Pro subscrition. I tried to install a Gateway on my maachine but it never shows up under "Manage Gateways" on the Power BI Service.

 

In short I am stuck in a weird no man's land where as flow doesn't support excel with simple formulae and Power BI service doesn't allow to create any calculated columns in the report I have to use .pbix as a middle man. If I expose my excel file directly to the Power BI service, it refreshed as advertised every hour but it is useless for me as I can't create report with the raw data. And I can't add additional fields in the file as Flow service doesn't like it. Any thoughts on this subject will be much appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
v-haibl-msft Super Contributor
Super Contributor

Re: Issue with refreshing .pbix file to power bi service

@ankur_raj

 

If the path shows c:\users\xxxx\onedrive, it will be recognized as local data source. You need to follow this document to get excel from OneDrive in PBI Desktop. Then the path will show as below.

Source = Excel.Workbook(Web.Contents("https://***-my.sharepoint.com/***/Documents/Issue%20with%20refreshing%20.pbix%20file%20to%20power%20bi%20service.xlsx"), null, true)

 

Best Regards,

Herbert

7 REPLIES 7
v-haibl-msft Super Contributor
Super Contributor

Re: Issue with refreshing .pbix file to power bi service

@ankur_raj

 

I think you installed the personal gateway which will not be shown under "Manage Gateways" on the Power BI Service. You need to check it in datasets settings.

BTW, the personal gateway is only available with Power BI Pro. You can refer to this document for more details.

 

Issue with refreshing .pbix file to power bi service_1.jpg

 

Best Regards,

Herbert

ankur_raj Frequent Visitor
Frequent Visitor

Re: Issue with refreshing .pbix file to power bi service

Thank You, I will try installing an On-Premise Gateway and try with that. But I am still not sure why a .pbix query can't be manually refreshed in the Power BI Service when the data file is stored on OneDrive for Business. I will expect when the refresh button on the Power BI service is clicked, it should fetch the data from the source which is not the case and I have to jump through hoops to install the gateway which makes sense only when the data is stored on a local machine or a non OneDrive environment.

 

Having said that, Thank You again for providing this valuable insight.

v-haibl-msft Super Contributor
Super Contributor

Re: Issue with refreshing .pbix file to power bi service

@ankur_raj

 

Where is your data source? If data source in an excel file which stored in OneDrive Business, we can get data from this excel file in Power BI Desktop. And then store this PBIX file in OneDrive Business. In that way, we can get the PBIX file in Power BI Service refer to this document. The refresh will not need any gateway as below.

 

Issue with refreshing .pbix file to power bi service_2.jpg

 

Best Regards,

Herbert

ankur_raj Frequent Visitor
Frequent Visitor

Re: Issue with refreshing .pbix file to power bi service

Hi Herbert

     My scenario is exaactly as you have described. I have an excel file sitting on OneDrive which is getting updated by Flow. I created a .pbix file which is sitting on OneDrive. I imported the .pbix file into the Power BI service and when I hit "Refresh Now" button under "Datasets", nothing happens. When I select option "Schedule Refresh" under Datasets I get the below screen. I have followed the steps mentioned in the document but .pbix file is not getting refreshed unless I open it in Power BI Desktop and hit "Refresh" button and publish it. Is there any specific setting I need to do in the .pbix file to get it refreshed automatically? I have created the .pbix file using Power Bi Desktop version 2.40.4554.463 64-bit (October, 2016). Another thing to note is the folder in which source excel file and .pbix file exist are both synced to my desktop, so when I see the path of excel file in the .pbix file it shows c:\users\xxxx\onedrive - campbell soup co\xxxx\batch completion timing.xlsx, not sure if that will cause the issue.

 

Thanks & Regards,

Ankur

Power BI Settings.PNG

 

v-haibl-msft Super Contributor
Super Contributor

Re: Issue with refreshing .pbix file to power bi service

@ankur_raj

 

If the path shows c:\users\xxxx\onedrive, it will be recognized as local data source. You need to follow this document to get excel from OneDrive in PBI Desktop. Then the path will show as below.

Source = Excel.Workbook(Web.Contents("https://***-my.sharepoint.com/***/Documents/Issue%20with%20refreshing%20.pbix%20file%20to%20power%20bi%20service.xlsx"), null, true)

 

Best Regards,

Herbert

Highlighted
ankur_raj Frequent Visitor
Frequent Visitor

Re: Issue with refreshing .pbix file to power bi service

Thank You Herbert for your patience and help with this issue. I am able to make it work the way linked document showed. Only thing I will add is that after making the source change and importing .pbix file to the Power BI service, I had to hit the publish button in the Power BI Desktop to have the new options appear for the dataset under "Schedule Refresh". Under "Schedule Refresh" option I was getting an error for wrong credentials and I had to use OAuth2 method for updating the credentials, Windows and Basic authentication options didn't work.

 

I wish this document you referred was part of the main documentation for data source connectivity and refresh article, I performed lot of search on the topic and couldn't find this article.

 

Thanks & Regards,

Ankur

pranim Regular Visitor
Regular Visitor

Re: Issue with refreshing .pbix file to power bi service

Hi,

 

I am stuck in same situation. How did you manage creating calculated columns , creating Dashboards and refreshing? Are you maintaining seperate excel sheet without calculated columns? I imported the excel sheet to power Bi desktop as mentioned in the document link and refresh is giving me problem calculated columns are not getting refreshed. Do i have to get Power Bi pro?  I haven't installed any gateway as my data is in One Drive for Business. And if i have to install a gateway my laptop has to be online, which is not possible. Please help me

 

Thanks in advance!