Power Query to Extract Data based on multiple criteria

Hello, would someone be able to help me with this? Thanks

I am on Excel 2016. What do i need to update? Thanks

Thank you so much!! Really appreciate your patience and help . Just updated it. Thanks

I don't know what you're to accomplished here, and I'm not an expert either.

From what you're trying to accomplished, I'd say that PowerPivot is the way to do it.

I'd use PowerQuery to make my data into tabular form, and from there, I'd then import it to the data model, and using DAX, I'd then create the P&L.

Thank you for your honest response. I appreciate that. Are you good with Macro's? I was thinking this can be done with a Macro as well if no one can help me? Thanks

I know how to use a normal pivot table but not sure about power pivot. Thanks

Although not an expert, I do know macro.

But doesn't matter if you want it to be formula or macro, you'd need to give a sample of how the 'original' is and the 'desired result'.

From the uploaded file that you had, I see that, there's formula that's still looking outside of the current workbook.

Attached is a snapshot of the work that I've done with PowerPivot for P&L

This should work for me too. In the attachment that i had posted, SAP CY is the data dump from SAP . We want to be able to populate the HEM P&L tab using two criteria to look up values from the SAP CY tab namely the Brand and P&L coding from the SAP CY tab. So if the Brand is "30" and the P&L coding is say "3" then pull in gross sales, if the brand is 30 and the P&L coding is "5" then pull in rebates and free goods. Currently i am using a sum if function but would like to automate this so that the only thing i need to do is to dump data into the SAP CY tab. Let me know if anything that i tried to explain is not clear. Thanks

Hi Philipsfn,

Doing with PowerPivot, although in the very beginning is very cumbersome (especially DAX), but the end result is very rewarding, as like you wanted, you just have to update the SAP CY and like pivot table, when you refresh, all the new data is 'populated'.

But since it takes a tremendous amount of work and to get to know your data, structure, calculation, what you want users to see etc, it's very difficult to do.

Formula and/or macro could do what you wanted to accomplished, the only downside would be if there's "new data" that's not captured in the P&L, then it'll be very difficult to know, unless you have a check in place to ensure that the amount that's shown in the P&L is indeed the same as the data pull from SAP.

Again, like I've mentioned in my previous reply, I saw formula that's looking on other workbook. So in order to incorporate formula and/or macro, one needs to know if the workbook name will always change every month etc.

I've also read what you've wrote which I quote "... ... populate the HEM P&L tab using two criteria to look up values from the SAP CY tab namely the Brand and P&L coding from the SAP CY tab. So if the Brand is "30" and the P&L coding is say "3" then pull in gross sales, if the brand is 30 and the P&L coding is "5" then pull in rebates and free goods ... ..."

When I look at your attached file, in the HEM P&L tab, I don't see a formula that look on 'SAP CY' tab, instead it's looking at an external workbook.

Also, when I looked at 'SAP CY' tab, for Brand "30", I have many P&L codes associated to Brand "30"...but I don't see P&L coding of 3 or 5

Hi Dluhut,

The tabs will have the same name each month. The only thing that will change is the data in the SAP CY tab. If you filter on columns C & D which is the brand and P&L , filer on Brand 30 and bring up P&L of 003 you will see that is the gross sales number which ties back to what is on the HEM P&L Tab. You are correct that this tab is populated by another tab which is basically the same SAP CY data except the person working on this file filtered it to show only HEM product Data. Keep in mind there are about 4 separate products, and for purppose of illustration i only showed here one product. We want to be able to create a P&L tab for each product using the SAP CY tab which is the master data. Hope this clarify's it a little better. If not clear, please do let me know. Thanks again for your time with this.

If that's the case, SUMIFS formula does work in this case.

in 'HEM P&L' tab, in cell C8, paste the formula below

Please Login or Register  to view this content.

Also, you need to identify, what brand goes to which 'Net Sales categories'...i.e. You've mentioned that 'Gross Sales' and 'Rebates and free goods' are Brand 30

You just need to ensure that whenever a user copy and paste the date to 'SAP CY', the column name should be the same, especially those that you're of interest, such as 'Brand' is on column C, 'P&L' is on column D and 'Net Month' is on column E etc.

Thanks, Can we build a macro to do this? I wanted to make this as foolproof as possible . Currently i am using this exact formula to update this. Someone suggested Power Query that is why i asked. Can we use a power pivot to accomplish this? Thanks

I read but I need to ask question
you want all columns from SAP CY filtered by C (30) & D (003) on another sheet via PowerQuery?
or I misunderstood...

Correct. Brand 30 could have multiple P&L items . I just used 30 & 003 for illustration purposes. There are 4 brands in all. 30, 35,25 & 27 each of these have multiple P&L codes. We want to be able to extract for each brand these multiple P&L codes. Thanks

Thanks again. 4 different tables with all columns. Is this possible? so on one tab it will be brand 30 and all the P&L combinations, on the second tab , brand 35 and all P&L combinations etc. Thanks

Thank you so much for your help with this. I didnt realize Power Query had so many steps to go through. Even after extracting the data i would still have to use SUMIFS function to get it into the P&L tab. Is there another way to get data from SAP CY to the product P&L tabs rather quickly? Thanks

If that's what you want, can't we not use a regular pivot table to do this?

And then from there, just use a 'GETPIVOTDATA' formula?

If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it already - ignore it.
Thank you.

Thank you. I will play around with this and let you know how effective it is. The step by step instruction was very helpful.

You are welcome and thanks for the feedback