asked on

Using PowerPivot/PowerQuery on multiple pc/users sharing the datasource connections

I have a pivot table that has multiple connections from 2 SQL database tables. I have tried multiple times to get another users pc setup so that they are able to refresh the Pivot table data. I tried with a direct ODBC connection to the SQL Tables and also Creating an .odc file placed in their "My Datasource" folder.

I tried to refresh the data connection string and it fails saying that not a valid path see administrator.

I am looking for the best solution in setting up a user so that they are able to manipulate the Pivot table. Please keep in mind that the original Pivot was created on 64bit pc and the user is 32bit, would that make a difference?

Russell Fox

Yes, it could be trying to use a 32-bit provider. Can you post the connection string (change passwords/usernames/servers to something random for security)?

Karen Schaefer

ASKER

here is what is currently displayed for datasource for the 64 bit system.

Power Query Connection
let
Source = Sql.Database("labqc1-sql.eng.mobilephone.net", "lte_lte_integration_db"),
#"td_ALL LTE DEFECTS" = Source{[Schema="td",Item="ALL LTE DEFECTS"]}[Data]
in
#"td_ALL LTE DEFECTS"

let
Source = Sql.Database("labqc1-sql.eng.mobilephone.net", "tpr_qe_db"),
#"td_ALL STATUS CONSOLIDATED DEFECTS" = Source{[Schema="td",Item="ALL STATUS CONSOLIDATED DEFECTS"]}[Data],

ODBC Connection on 32 bit:

Connections Property:

Provider=Microsoft.Mashup.OleDb.1;Data Source=$EmbeddedMashup(6ce2e52d-021a-4d02-9b48-8a1c25a64bff)$;Location=TPR;Extended Properties=UEsDBBQAAgAIAAOE/ERoPNvEqgAAAPoAAAASABwAQ29uZmlnL1BhY2thZ2UueG1sIKIYACigFAAAAAAAAAAAAAAAAAAAAAAAAAAAAIWPQQ6CMBREr0K655e2StB8ysKtJCZE47aBCo1QDBThbi48klfQRDHu3M28vMXM43bHZGpq76q73rQ2JgwC4mmbt4WxZUwGd/IjkkjcqfysSu29ZNuvp76ISeXcZU3pOI4wCmi7kvIgYPSYbrO80o0iX9n8l31je6dsronEw3uM5MAEiDCKgC840hljauycGSxB8FUIAdIfjJuhdkOnpbb+PkM6V6SfH/IJUEsDBBQAAgAIAAOE/EQPyumrpAAAAOkAAAATABwAW0NvbnRlbnRfVHlwZXNdLnhtbCCiGAAooBQAAAAAAAAAAAAAAAAAAAAAAAAAAABtjksOwjAMRK8SeZ+6sEAINWUB3IALRMH9iOajxkXhbCw4ElcgbXeIpWfmeebzelfHZAfxoDH23inYFCUIcsbfetcqmLiRezjW1fUZKIocdVFBxxwOiNF0ZHUsfCCXncaPVnM+xxaDNnfdEm7LcofGOybHkucfUFdnavQ0sLikLK+1GQdxWnNzlQKmxLjI+JewP3kdwtAbzdnEJG2UdiFxGV5/AVBLAwQUAAIACAADhPxE05cnbUkCAACtBQAAEwAcAEZvcm11bGFzL1NlY3Rpb24xLm0gohgAKKAUAAAAAAAAAAAAAAAAAAAAAAAAAAAAjVNbi+IwFH4X/A8h+1KhU7bDsi+DD53WAUFGmXafRCS2Ry00iSanw4r43zdptbZu9+KL5OSc73LyVUOKuRQkrv/9l+FgONB7piAjyeKDjEkBOBwQ84tlqVIwlfhYeBFDtmEaHFqwzTH1n7Qpgth5XG7yAg57KcATgNQlFA9qfYR1tqEjt4b6QjFbB7MZiZMg+RGTcP4ez2fTKEgmEYkmb5MwiallqijPyzjdA2djM0XdKQIf038Nry5LK3F1JYwPRY6hLEouFlIb5IRtCvBaZee/NLk0hk9QOZ6oWw0jqBolgZ/4ejLgud2jds5fXeJfXIKqhJF7buY8366kOT3Ty20p4Z6JHWTJ6QCNwEQxobdS8VqkvdNO14x7fgBHC4BGjmE/t5lcMhX4/ZtnUS4N7QcIxiGr8e6rqcvXqtPS1uF7bpuhlz4v/t/NdOkteARowmjyNxc3NxlDqNyEhdS/3zS0U6FBmdGw1Ch5QxxkV/y2D98otxGpM2bwgKV7Qk3oaT+c34PX7bCIpWI2ADe829l8MCftNKc3JbmzbNysnsiyZXs1GvW/jv+H53kQ2tliLkh4SgvrkC6UzMoUDWoB5uPVtB0DLj/7YmDLN54HNSbWoSwFErklEWwNn+6Eu4p605GAxtSykmC7rbTZ63qOxCXnTJ2qASW1Nnq2oEBUL2MwOAdRowda5zthjKGkLfVSZaa/R391cTfQdmn0LwqGNpKdHN9lvZd8A6p3dVVTO6mdcDa+kGHZbX6tCdrRu8fmMhoOctFv6uUXUEsBAi0AFAACAAgAA4T8RGg828SqAAAA+gAAABIAAAAAAAAAAAAAAAAAAAAAAENvbmZpZy9QYWNrYWdlLnhtbFBLAQItABQAAgAIAAOE/EQPyumrpAAAAOkAAAATAAAAAAAAAAAAAAAAAPYAAABbQ29udGVudF9UeXBlc10ueG1sUEsBAi0AFAACAAgAA4T8RNOXJ21JAgAArQUAABMAAAAAAAAAAAAAAAAA5wEAAEZvcm11bGFzL1NlY3Rpb24xLm1QSwUGAAAAAAMAAwDCAAAAfQQAAAAA

Open in new window

Russell Fox

Have you installed Power Query on the user's machine?

Karen Schaefer

ASKER

yes

Russell Fox

Gotcha. That image is just a SQL Server connection issue: I don't see where ks540x is specified as the user in the connection - is that the local user? Does that user have select access on the SQL tables used by the pivot table?

GRANT SELECT ON dbo.MyTable TO ks540x;

Also make sure the SQL server allows remote connections:

Right-click the SQL Server instance name -> Properties -> Connections -> Allow remote connections to this server

ASKER CERTIFIED SOLUTION

Karen Schaefer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Russell Fox

No worries, I've certainly been there!

Karen Schaefer

ASKER

award points for the contributors time - solved issue