locked
Delete Rows in Power Pivot Data model RRS feed

  • Question

  • I have transaction table (Sales)  in sql server table and master data(Customer) is availabe in excel file. These 2 different data information I imported into Power Pivot data model for excel 2013. and provided relation between these 2 tables.

     Now Requirement is I need to delete rows in tranasaction table, the customers which are not available in customer master (Excel)

    Can some one help on this.to resolve this issue.

    Regards,

    Praveen


    Regards, Praveen

    Thursday, May 29, 2014 7:46 AM

Answers

  • Hi Praveen,



    The approaches described so far won't physically remove the rows from the Sales table in Power Pivot; they will only provide a means to filter/hide them in Pivot Tables, Pivot Charts, Power View, etc (please see Martina's example above).

    Is the ability to use the existence check column as a filter in Pivot Tables/Pivot Charts not sufficient? 

    To remove the rows before loading them into the Power Pivot model, you'd need to exclude them outside of Power Pivot in which case Power Query comes to mind: http://office.microsoft.com/en-gb/excel-help/merge-queries-HA104149757.aspx?CTT=5&origin=HA103993872).


    In the case of the DAX formula error, you're getting it because the calculated field must return a single value, but the expression used would return more than one row and column.


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn

    • Edited by Michael Amadi Friday, May 30, 2014 11:08 AM Small edits
    • Proposed as answer by Charlie Liao Sunday, June 8, 2014 10:06 AM
    • Marked as answer by Charlie Liao Monday, June 9, 2014 2:03 PM
    Friday, May 30, 2014 11:02 AM

All replies

  • Have you considered using LEFT OUTER JOIN in the model?

    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Thursday, May 29, 2014 7:58 AM
  • Hi Raunak,

        I have 2 different data sources like one is Sql Server table and Another is excel file. Just I import all data into Power pivot data model and created a relation between these 2 tables.

    I don't know how to write Left outer join between 2 different sources. Can you please guide me how to do this.

    Regards,

    Praveen C


    Regards, Praveen


    • Edited by Praveen.C Thursday, May 29, 2014 8:17 AM
    Thursday, May 29, 2014 8:14 AM
  • Hi Praveen,

    An option is to add two calculated columns to your Sales table to identify the items that don't exist and then filter them out in your pivot table. You can use a lookup column to capture those items which exist in your Customer table:

    Use your values, such as =RELATED(CUSTOMER[CustomerID])

    And then add another column to identify whether or not the first column is blank.

    =if(ISBLANK(SALES[CustomerLookup]),"NOT EXISTS","EXISTS")

    Now you can use this CustomerExists column as a filter in your Pivot Table.  Filter where CustomerExists = EXISTS.


    Martina White

    • Proposed as answer by Michael Amadi Thursday, May 29, 2014 10:11 PM
    Thursday, May 29, 2014 8:04 PM

  • Martina White

    Thursday, May 29, 2014 8:05 PM
  • Hi Praveen,

    An option is to add two calculated columns to your Sales table to identify the items that don't exist and then filter them out in your pivot table. You can use a lookup column to capture those items which exist in your Customer table:

    Use your values, such as =RELATED(CUSTOMER[CustomerID])

    And then add another column to identify whether or not the first column is blank.

    =if(ISBLANK(SALES[CustomerLookup]),"NOT EXISTS","EXISTS")

    Now you can use this CustomerExists column as a filter in your Pivot Table.  Filter where CustomerExists = EXISTS.


    Martina White

    Building upon what Martina has already proposed, you could create a calculated column called 'CustomerPresence' that uses a single DAX formula to indicate the customer's existence:

    =
    IF(
      CALCULATE(COUNTROWS(Customers)) > 0, 
      "Has Customer",
      "Has No Customer"
    )

    You would then go on to use this as a filter as described by Martina.

    Another variation of the approach could be to return a TRUE/FALSE value instead of a text/string and use it when defining calculated columns against the Sales table. This calculated column could be called 'CustomerPresenceFlag' and defined as:

    =
    IF(
      CALCULATE(COUNTROWS(Customers)) > 0, 
      TRUE(),
      FALSE()
    )

    And then a calculated field that uses it could be defined as:

    TotalAmount:=
    CALCULATE(
      SUM(Sales[Amount]), 
      Sales[CustomerPresenceFlag]
    )

    The calculated field, 'TotalAmount' in this example, will only consider Sales rows that have a corresponding CustomerPresenceFlag value of TRUE.

    You can get an idea of how this has been applied by taking a look at the following:

    Edit: Please pardon the 'TransactionID' typo ;)

    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn



    • Edited by Michael Amadi Thursday, May 29, 2014 11:22 PM Small edit
    Thursday, May 29, 2014 11:16 PM
  • Hi Praveen and Michael,

    I considered an approach like Michael has described, and it is effective.  You would have to create a calculation for every measure in the Sales table.  Using a flag to identify sales rows where the customer exists means you only need the one filter, rather than a calculation for every measure in your sales table.  Both methods are effective depending on your circumstances.  

    Cheers,

    Martina


    Martina White

    Friday, May 30, 2014 12:38 AM
  • Hi All,

         I followed same steps, but I  can see both Exists and Not  EXISTS status in my reference column. As per my requirement I need to completely filter Not  EXISTS data.

    For this I written below DAX query it is returns an error like

    DAX Query :=FILTER('Customer','Customer'[Ref]="Not  Exists")

    Error Message:The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

    Can you please  help on this.


    Regards, Praveen

    Friday, May 30, 2014 6:25 AM
  • Hi Praveen,



    The approaches described so far won't physically remove the rows from the Sales table in Power Pivot; they will only provide a means to filter/hide them in Pivot Tables, Pivot Charts, Power View, etc (please see Martina's example above).

    Is the ability to use the existence check column as a filter in Pivot Tables/Pivot Charts not sufficient? 

    To remove the rows before loading them into the Power Pivot model, you'd need to exclude them outside of Power Pivot in which case Power Query comes to mind: http://office.microsoft.com/en-gb/excel-help/merge-queries-HA104149757.aspx?CTT=5&origin=HA103993872).


    In the case of the DAX formula error, you're getting it because the calculated field must return a single value, but the expression used would return more than one row and column.


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn

    • Edited by Michael Amadi Friday, May 30, 2014 11:08 AM Small edits
    • Proposed as answer by Charlie Liao Sunday, June 8, 2014 10:06 AM
    • Marked as answer by Charlie Liao Monday, June 9, 2014 2:03 PM
    Friday, May 30, 2014 11:02 AM