• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

1st time using Power Pivot - Please help

lwilt

Member
I'm trying to set up a power pivot to dump order data into a pivot table. When I go into Manage in the Power Pivot I can not find the existing connections set up. When Normally using excel and pulling in data I just go to data>from other sources>MS query and I can see the database connections I'm trying to get to so that I can bring in the info I need. What do I need to do to have those same connections come up in Power Pivot? Can someone please walk me through the set up process for how I need to do this.

Also, I would need to bring in another excel file, items I only want orders to show if they have those items, into the power pivot. How would I do that? I assume once it's in there just setting a link with it and the table that has the data coming into the power pivot to get it to only show those orders.
 
I see add to data model in PowerPivot but can't find the power query anywhere.

Do you just mean using the MS query to bring in the info and then using add to data model in PP to bring over what I need into PowerPivot?

And I'm using 2013 excel
 
You can get PowerQuery from below.
https://www.microsoft.com/en-ca/download/details.aspx?id=39379

Note:
  • Microsoft Office 2013:
    • Power Query Premium: All Power Query features available for: Professional Plus, Office 365 ProPlus or Excel 2013 Standalone
    • Power Query Public: Available for all other Office 2013 Desktop SKUs. Includes all Power Query features, except the following ones: Corporate Power BI Data Catalog, Azure-based data sources, Active Directory, HDFS, SharePoint Lists, Oracle, DB2, MySQL, PostgreSQL, Sybase, Teradata, Exchange, Dynamics CRM, SAP BusinessObjects, Salesforce.
 
I need 2 tables from an ODBC connection and 1 excel sheet all coming into the PP. I'm assuming for the power query just load both tables I need from ODBC which creates 2 sheets, one for each.

So then have 3 total sheets and use data loaded in PP to bring in all 3 sheets to set my connections and start grabbing fields.

Does that sound right?
 
Crap wouldn't load the first table. Got to ~640,000 rows then had an error and couldn't load anymore.
 
It won't let me load the entire first table to the spreadsheet or data model. Just says load failed.
 
Normally what I would do is create PowerQuery connection, do data modeling there (joins, and other calculations). Then load it into Data Model.

No need to load the data into sheet.

What's your data source by the way (Access, text file etc) and what version of 2013 (32 bit or 64 bit)?
 
I tired just doing the load as load connection instead of bringing the table into excel first. On a smaller table, ~50,000 rows, it works fine and creates the connection for me to use in PP. But the tables I need are much bigger tables, ~700,000 lines in each table. When I try doing those two it gets maybe 98% there then fails. I don't need the entire table and it doesn't appear that PowerQuery allows you to restrict within the table before bringing it over or the connection like you can in a standard excel MS query with that connection.

2013 32 bit.

Source I'm going: From other sources>From ODBC> picking the ODBC connection that has the tables I need.
 
Last edited:
Do you know anyone who has done that and it worked or if they ran into any problems?

Got our IT guy at work right now looking to see if he can find a copy of 64 bit 2013 excel but I don't think there's a copy here.
 
Didn't realize we had office 365...I can just download the 64 bit version from there and I should be good to go.
 
I used it without issue. But I don't have any add-in except for PowerQuery, PowerPivot and Solver on the machine that I used it on.

Yes, that should solve memory issue. Also, MS just recently released update which increases memory usage for Office 365 (Excel 2016) 32 bit version running on Windows 32 bit to 3 Gig and 64 bit to 4 Gig.
https://support.microsoft.com/en-us/kb/3160741
 
hmm when I downloaded 64 bit office from microsoft 365 it ended up downloading 2016 office.

it says power query comes with it in the data tab but I can't seem to find it. Can I still do what you were suggesting with 2016?
 
Depends. What version of Office 365 subscription do you have?

PowerQuery comes as standard for all subscription. Now called Get & Transform.

However, PowerPivot is only available for Pro Plus subscription or Standalone Excel.

Keep in mind that if you have any other MS Office product in 32 bit. You will need to change that to 64bit version as well.

From your data size, I'd think 32bit version will work fine with latest update.
 
Ok found it...not sure how I missed that under the data tab. and it's ProPlus.

Yeah I uninstalled office and reinstalled it from office 365 so it's all 64 bit.

Last question....I hope lol. When I goto get & transform>new Query>other sources>ODBC...it asks for a connection string. What is that? I tired the server name but it didn't like that so I wasn't sure what it's looking for.
 
Sweet that worked....really appreciate all the help man.

1 quick side question. When I'm in PP manage is there a quicker way to delete unwanted fields without having to highlight columns and right click delete since it doesn't allow you to scroll across.
 
Back
Top