Solved

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

Posted on 2014-07-28
9
658 Views
Last Modified: 2014-08-03
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?
0
Comment
Question by:Karen Schaefer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40225765
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)?
0
 

Author Comment

by:Karen Schaefer
ID: 40227252
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:
pic
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

0
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40227308
Have you installed Power Query on the user's machine?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Karen Schaefer
ID: 40227317
yes
0
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40227432
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
0
 

Accepted Solution

by:
Karen Schaefer earned 0 total points
ID: 40227626
Turns out I misinterpreted the issue with the user.  After having the use step thru the issue we realized that turns out that Power Pivot Table functions slightly different from the old Pivot table and the problem was  a User learning curve issue.  instead of using the properties window to change the look of the Pivot Table - the user was attempting to click and drag from the Report filter within the Pivot table and not within the Properties windw.

Thanks for your time
0
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40227991
No worries, I've certainly been there!
0
 

Author Comment

by:Karen Schaefer
ID: 40237110
award points for the contributors time - solved issue
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Lookup - Vlookup 9 46
Excel 2016 lost MRU list 8 67
add a column label to a list object using VBA 2 21
Tricky Shapes formula part 4 4 13
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question