Delete rows from a Power Pivot table

[Vivek D]

I have a power pivot table that gets it's data from a view from a local DB on my PC. Don't ask me why local DB. It's just a temporary solution until a full-blown solution is put in place.

I need to distribute the report to all business unit (BU) leads and sub-business unit leads with just their organization information.

Since that's a lot of them I want to do just the business unit level reports and transfer the job of getting the sub-leads their reports to someone in the business unit org.

In short, I will generate the BU level report and then send it out to someone on the BU team. That person should then be able to delete data not relevant to a sub-BU and send the report out to that lead and basically repeat this process for each sub-BU within his/her BU.

So question is - Is there a way to delete records from a table in the Power Pivot data model.

 

[r1c1]

@Vivek D

You can do this in a couple of ways.

If your users have Power Query too:
Set up a query that reads speadsheet table to determine which rows to keep and use the value to filter the table in PQ. Then load the table to data model and access it in PP. For more instructions either play with PQ or search online.

If your users have only PP:
Set up a connection in PP by Query (during the connection process you can edit the query). Add Where clause to the query to filter records by BU. Instruct your users to edit this query when they first load the data. After that whenever they refresh the workbook connections, they will only get BU specific data. For more on how to add WHERE clause, read about SQL Where clause in either books or SQL websites.

All the best.

 

[NARAYANK991]

Hi Vivek ,

Some more information :

https://social.msdn.microsoft.com/F...932d1d7/delete-rows-in-power-pivot-data-model

http://www.powerpivotpro.com/2009/1...hree-options-with-a-football-angle-of-course/

Narayan

 

[Vivek D]

@Vivek D

You can do this in a couple of ways.

If your users have Power Query too:
Set up a query that reads speadsheet table to determine which rows to keep and use the value to filter the table in PQ. Then load the table to data model and access it in PP. For more instructions either play with PQ or search online.

If your users have only PP:
Set up a connection in PP by Query (during the connection process you can edit the query). Add Where clause to the query to filter records by BU. Instruct your users to edit this query when they first load the data. After that whenever they refresh the workbook connections, they will only get BU specific data. For more on how to add WHERE clause, read about SQL Where clause in either books or SQL websites.

All the best.

Hey Chandoo.. Nice to see a response from The MAN Himself.

PQ Option: Unfortunately, users are still on Office 2010 and don't have PQ by default. I could get them to install it but hopefully in a few months they will all be upgraded to 2013 at least and then PQ option could be used. For now though, I was trying to see if there was an alternative without using PQ.

PP Option: Since the data is in my local DB that no one can access, users can't really refresh and get data. So this option isn't quite possible right now either.

 

[Vivek D]

Hi Vivek ,

Some more information :

https://social.msdn.microsoft.com/F...932d1d7/delete-rows-in-power-pivot-data-model

http://www.powerpivotpro.com/2009/1...hree-options-with-a-football-angle-of-course/

Narayan

Hi Narayan... I did go through these before posting the question. I guess given there aren't any other simpler options I can try a combination of Chandoo's suggestion of using PQ and combine that with a linked table approach.

So I will use PQ and load BU specific data into a table and generate the 5 BU level reports. Then I will pass along these reports to the respective BU Teams. Individual BU Team representatives will then delete data from the linked table and refresh power pivot to create the sub-BU level reports and publish those to respective sub-BU leads.