locked
Data Duplicated while Copy/Pasting Filterd Data from PowerPivot to Excel RRS feed

  • Question


  • My PowerPivot worksheet contains total 70,000 rows.

    Now based on some filters my data size reduced to 55,000 rows in PowerPivot.

    When I try to copy & paste this data several times in Excel I'm getting some duplicate values i.e. the filtered data pasted in Excel is incorrect data.

    Please suggest any solution on this.

    Wednesday, January 4, 2012 10:04 AM

Answers

  • Mayur

    why you would copy data from PowerPivot and paste it Excel. You could create multiple (Power)PivotTables on the same PowerPivot table/sheet. On each PivotTable you set the proper filtering. 

    If you want to avoid to set filters on pivottables its better to import the data in PowerPivot multiple times and set the filters in the queries. You Excel file will then be smaller too.

     

     


    Eddy Nijs
    • Edited by Challen Fu Friday, January 6, 2012 11:33 AM
    • Marked as answer by Challen Fu Thursday, January 12, 2012 12:17 PM
    Thursday, January 5, 2012 10:03 PM

All replies

  • Hi Mayur

    Why do not you use a flattened PowerPivottable? I suppose you want to show your data as a flat table. Is that right?


    Eddy Nijs
    • Edited by Eddy Nijs Wednesday, January 4, 2012 7:41 PM typo
    Wednesday, January 4, 2012 7:40 PM
  • Hi Eddy,

    I want to show only the data filtered in PowerPivot worksheet.

    I tried flattened PowerPivottable option, but It doesn't filterd the data.

    It shows all the rows which are present there even if u apply filter in PowerPivot worksheet.

    Thanks,

    Mayur

    Thursday, January 5, 2012 9:00 AM
  • Mayur

    What you could do is add a column with an IF expression referencing to the measure column, if the row applies to your filter you take the value of your measure else 0. You may hide the measure column from PowerPivot PivotTable and PowerPivot Table  (check in column properties). 

     


    Eddy Nijs
    Thursday, January 5, 2012 11:25 AM
  • By this approach it will become very restrictive for the user.

    Further I have more than 10-12 measures, applying IF condition on each will be very difficult!!

    Thanks,

    Mayur

    Thursday, January 5, 2012 12:02 PM
  • Mayur

    why you would copy data from PowerPivot and paste it Excel. You could create multiple (Power)PivotTables on the same PowerPivot table/sheet. On each PivotTable you set the proper filtering. 

    If you want to avoid to set filters on pivottables its better to import the data in PowerPivot multiple times and set the filters in the queries. You Excel file will then be smaller too.

     

     


    Eddy Nijs
    • Edited by Challen Fu Friday, January 6, 2012 11:33 AM
    • Marked as answer by Challen Fu Thursday, January 12, 2012 12:17 PM
    Thursday, January 5, 2012 10:03 PM