Gateway data source mapping of excel documents

[malraff]

hi all

i am trying to get our datasets updates automated using pbi gateway

the datasets have muliple sources, sql and excel based, the sql sources maps fine.

the excel data sources are dependand on the users folder mappings on a different server to the gateway.

when i look at the data source in the report i see

u:\\power bi\\working days.xlsx

i have tried to map these to gateway sources using fulll unc path

\\server\users\J.Doe\Power BI\working days.xlsx

but this fails to work ( i can setup the full unc source  in the gateway ok) it simply fails to map to the report source

If i try to setup the data source in the gateway using the same drive mapping etc, it wont connect at all - uable to find teh data source is the error

what is best practice to acheive this?

thanks

mal

[malraff · Accepted solution]

may help someone else...

when creating your datasources, ensure all excel files are declared using full unc paths, do not use mapped drive!

once i rebuilt the report, i could map the excel files in teh gateway without issue!

[v-rzhou-msft]

Hi @malraff 

Please use UNC path to get data source and republish agian.

Mapped drives are only available on your machine locally, which is not available on your network, the UNC path is the workable path for the shared drives in your network environment.

For the gateway service, it won't recognise the X:\ (Mapped drives) through the network, which is only a shortcut of the shared path on your local machine.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[v-rzhou-msft]

Hi @malraff 

Please check what kind of gateway did you use. 

Gateway has two version: Gateway (Personal mode) and Enterprise Gateway.

For reference: Use personal gateways in Power BI - Power BI | Microsoft Docs

I think you use import connection mode to connect to Excel file. Both Gateway(Pesonal mode) and Enterprise Gateway support to refresh Import data.

If you use Gateway(Pesonal mode), please make sure the data source and gateway installed in the same computer. And please make sure you didn't change the file location. You can refresh the report in desktop and if you can refresh successfully in desktop, the credential is right.

Then you need to edit credential in dataset setting in Power BI Service.

If you use Enterprise gateway, you need to configure your credential in Manage gateway.

For reference: Manage your data source - Import/scheduled refresh - Power BI | Microsoft Docs

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.