locked
Power Pivot off linked table instead of power query RRS feed

  • Question

  • Hello,

    I'm pretty new to power pivot. Usually the first thing I do is create a table via a data connection using a query I've written. Then I go up to PowerPivot and click on Add to data model. I've noticed that sometimes the data within the power pivot manager (and thus available in the power pivots) is either directly using the data from the query, or it's actually linked to the table I created within the workbook. Both places have the same data, but the difference is when it's linked to the table, I'm able to create calculated columns on the table and it pulls through to the power pivot manager. 

    I haven't figured out why sometimes it's linked to the table and sometimes it's pulling from the query directly. Like I said, I'm pretty new to power pivot, so it's easier for me to create calculated columns on the data table than figuring out the corresponding formula in DAX to write into the power pivot manager table.

    I'd like to know how to link the power pivot to the table vs the raw query and also how to switch it if I've already put some time into a workbook and don't want to start over.

    Thanks.


    - Addison

    Wednesday, December 30, 2015 4:02 PM

Answers

  • So in Power Query you can load data to a worksheet or load data to the model (or both). You'll want to always load to worksheet for this use case.

    Loading data to various destinations from Power Query.

    In Power Pivot you can add data from many sources. One of these sources is directly from an Excel table as a linked table. You'll want to add the Excel table you create with Power Query (loading to workbook and not to model) to Power Pivot by linking the Excel table to Power Pivot.

    Creating a linked table for Power Pivot.

    This being said, you're probably better served by performing the transformations and adding the columns you need in Power Query rather than in a native Excel table. With Excel you will be limited to 1M rows, whereas Power Query and Power Pivot do not have this limitation.

    GNet Group BI Consultant

    • Proposed as answer by Michael Amadi Thursday, December 31, 2015 8:44 PM
    • Marked as answer by Charlie Liao Thursday, January 7, 2016 9:55 AM
    Wednesday, December 30, 2015 4:55 PM
  • From Power Query, make sure you load the data to an Excel worksheet table.

    Add that table to the Power Pivot model.

    In Power Pivot, delete the table that is currently coming directly from Power Query.

    Rename the linked table identically to the now-deleted table direct from Power Query.

    Go back to Excel. Everything should be as it was in your pivot reports.

    If there are any measures defined in the table that will be deleted, you will have to copy them over to another table before deleting the table, else you will lose these measures.

    GNet Group BI Consultant

    • Proposed as answer by Charlie Liao Thursday, January 7, 2016 9:55 AM
    • Marked as answer by Madadd33 Thursday, January 7, 2016 4:02 PM
    Monday, January 4, 2016 10:57 PM

All replies

  • So in Power Query you can load data to a worksheet or load data to the model (or both). You'll want to always load to worksheet for this use case.

    Loading data to various destinations from Power Query.

    In Power Pivot you can add data from many sources. One of these sources is directly from an Excel table as a linked table. You'll want to add the Excel table you create with Power Query (loading to workbook and not to model) to Power Pivot by linking the Excel table to Power Pivot.

    Creating a linked table for Power Pivot.

    This being said, you're probably better served by performing the transformations and adding the columns you need in Power Query rather than in a native Excel table. With Excel you will be limited to 1M rows, whereas Power Query and Power Pivot do not have this limitation.

    GNet Group BI Consultant

    • Proposed as answer by Michael Amadi Thursday, December 31, 2015 8:44 PM
    • Marked as answer by Charlie Liao Thursday, January 7, 2016 9:55 AM
    Wednesday, December 30, 2015 4:55 PM
  • Excellent, this is very helpful thank you. Would you know how to switch a source from a query to native excel table? I'd rather not start all over, so if I could keep my Pivots the same and just update the source that'd be best case. I'm well within the 1M row limit, so although it's better practice I believe I need it to offset my poor DAX skills. 



    - Addison


    • Edited by Madadd33 Monday, January 4, 2016 3:29 PM
    Monday, January 4, 2016 3:27 PM
  • From Power Query, make sure you load the data to an Excel worksheet table.

    Add that table to the Power Pivot model.

    In Power Pivot, delete the table that is currently coming directly from Power Query.

    Rename the linked table identically to the now-deleted table direct from Power Query.

    Go back to Excel. Everything should be as it was in your pivot reports.

    If there are any measures defined in the table that will be deleted, you will have to copy them over to another table before deleting the table, else you will lose these measures.

    GNet Group BI Consultant

    • Proposed as answer by Charlie Liao Thursday, January 7, 2016 9:55 AM
    • Marked as answer by Madadd33 Thursday, January 7, 2016 4:02 PM
    Monday, January 4, 2016 10:57 PM