Refreshing PowerBi Online using Personal Gateway Issue

[jonnychenoweth]

All,

This is a very odd issue and I assume that I am doing something super simple wrong. I have both Desktop PowerBi and PowerBi online (service). I have a dataset that is comprised of two XSLX excel files which are essentially hitting a Jira web endpoint which downloads a .csv file and updates the .xlsx files using Web source (which happens to be an INTERNAL on prem Jira instance). From there I have a full dashboard and visuals that utilize that data in the Desktop PowerBi. I have those two excel .xlsx files being updated every 5 mins via a simple batch script (although did it with VBA and VBS as well). That is all working great and even if I hit the "Publish" button in PowerBi desktop version it will update the visuals in the online (App.powerbi.com) instance just fine. However it ONLY works if I hit publish, I have4 googled and spent 30 hours banging my head against the wall. n

Now I have also installed the Personal data gateway since I am trying to pull data from an Internal on prem instance of Jira within my network and that appears to be working fully with no issues. I tested this with test dummy data although not from Jira as that isn't working.

What I noticed is that when I use a simple raw data .csv file I can use that as a source and when I go into the settings in the online powerbi I can see and turn on the gateway connection. See image.

However, when I go into the the main dataset where I have the two .xlsx files updateing my main data it is grayed out and I can't flip the switch. I am 100% or nearly positive this is because the data source in the .xlsx files is a WEB call. However what I don't get is that yes those files are webcalls but in the .pbix file locally I pointed it to just the two .xlsx files and so assumed it would show that as the data sources just being the excel files and let the files call the web. It seems as that isn't the case....and it inherits it fully and thus no way I can see to use the data gateway (personal).

Also the credentials are correct, it is failing because app.powerbi.com cannot obviously see our on prem jira thus the reason for  the data connector in the first place.

Also again this all works flawlessly if I press "Publish" in the Desktop powerbi. Even the automatic refresh of the data via batch file is working I just can't get it to connect to the online powerbi.

Any ideas I woudl so deeply appreciate it. We are eventually going to get the Jira connector setup etc. which will make this 100% easier but I don't control that and am told that is 6 months out...

Thank you for any time responding if you can give me any ideas.

Thanks!

[v-jayw-msft]

Hi @jonnychenoweth ,

Has the issue been solved? If not, can you share the detailed error message?

Best Regards,

Jay

[jonnychenoweth · Accepted solution]

Yes for whatever reason, when I recreated it again and left the excel files as stand alones and just had it set to auto refresh on open. I then used a batch file to open them every hour while using the data gateway personal and it worked. Just had to delete and recreate it all.

[Jeanxyz]

I notice your gateway is empty, those csv data source are not appearing in your gate. Based on your solution, I guess the csv files are saved in your local drive?  You only need to add files to gateway if the files are saved on SharePoint or One Drive. 

[jonnychenoweth]

They are saved in one drive. It is working now, just had to recreate it. Still don't know why it converted my local files/one drive xls to the Jira on prem URL/web but it only happened once. It has since been working.

[jonnychenoweth]

In some ways another solution would be that if there was anyway to make my main .pbix file not inherit the data source from the .xlsx files and instead just BE the .xlsx files that would solve this 100%.

If there is a way to change that from jira on prem (local web) to be XXXXX.XLSX file I could then use the data gateway personal and it would allow it (per my tests).

Thanks again.