Read csv from network location in service

[po]

Hi,

Have a power bi desktop report which reads .csv files from a shared network drive.

I want to read these files using the service in order to automate.

A couple of questions can the service also read these shared network files if the gateway machine has the ability to see these shared files?

Is there a better way of accomplishing this longer term - not sure of one drive has been rolled out to everyone yet in our organization.

Thanks

[ibarrau]

Hi. Yes you can. The service can read files shared in network if the VM with the gateway is shared. The key is to have the same path in the power bi desktop and the manage gateway configuration in service (data source = file). Something like this:
path= \\ [IP where file is shared] \ [Folders] \ [File.csv]

The authentication should be a windows user. You have to complete the process login in with 

User: Domain\User

Password: YourPassword

Another way should be onedrive or sharepoint. In both cases you don't need a gateway and power bi service can connect directly

Hope this helps,

[po]

Hi,

Thanks for reply.

Have below in 

Source = Csv.Document(File.Contents("\\<server>\bi_shareddata\PowerBI\Reports\Workspaces\BI Team\Files\app_reports.csv")

I can refresh file in desktop o.k. and the gateway server can see the above fileshare.

Not sure what mean by "if the VM with the gateway is shared".?  How ca we check this?

Get error belwo if try and refresh on service.

Scheduled refresh has been disabled.

Please try again later or contact support. If you contact support, please provide these details.

• Data source errorScheduled refresh is disabled because at least one data source is missing credentials. To start the refresh again, go to this dataset's settings page and enter credentials for all data sources. Then reactivate scheduled refresh.

I can refresh other reports fine which have oracle data sources but have never tried to refresh a report using the service where data source is a csv on a shared network drive.

Also like idea of using sharepoint - we have a teams site which members of our team use but when try and use sharepoint.

These csv files down a tree as per below and the folders have spaces in them and when try and set up to access via sharepoint on power bi desktop.

https://<company>.sharepoint.com/:x:/r/sites/BI/Shared%20Documents/Power%20BI/Workspace%20Inventory/...

 get error message

If I try access  rro level I can see the namesof the csvs bt trying to access content of them in sharepoint.

[ibarrau]

Good. Next step should be adding the source on the "Manage Gateway" options like this doc:

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-manage

In order to make this work you need a Data Gateway installed on a PC/VM/computer. That pc must be able to read that path "\\<server>\bi_shareddata\PowerBI\Reports\Workspaces\BI Team\Files\app_reports.csv". You can check it in browser explorer for example. With this configured, you can add the source in power bi service in manage gateway with that computer windows credentials.

After the source is ok (green check), you can schedule refresh the report.

Hope this make sense.

[po]

Thanks for reply.

Have set up a new gateway source as per below and scheduled the refresh and looks to work o.k for the network share.

Have managed also to get sharepoint as source working in desktop.

However, when I try set up a data source of sharepoint in service get message below.

Any thoughts on how we can set up data source of sharepoint to work in the service with gateway?

I have managed to get thsi working without selecting gateway for the sharepoint source as says optional.

desktop has Source = SharePoint.Files("https://<company_name>.sharepoint.com/sites/BI/"

 and gateway server has permsiion to see this site.

Not sure what to put adavnace settings level - tried all but still not working.

Thanks again.

[ibarrau · Accepted solution]

The configuration I have mentioned is for a shared file in a local network. If you have sharepoint file you don't need to add it in the gateway. You can with auth2 credentials (AD). The windows credentials are just for the local file.

If you have a source in gateway and an online source you need to check this option in gateway configuration to make it work:

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-mashup-on-premises-cloud

With that option activated and the local network file configured in the gateway you might be able to schedule refresh. If you want you can add sharepoint to the gateway too but it's not necessary, you can just enter the credentials when configuring the schedule refresh.

[po]

Thanks for reply.  Using auth2 autnetication thus avoding gateway for the sharepoint source and looks to work o.k.