Power Query - filter based on cell outside table

Larry of Oz

New Member
Joined
Jun 2, 2020
Messages
17
Office Version
  1. 2016
Platform
  1. Windows
Hello legends,

I am teaching myself Power Query and having great results so far, but there's something I'm not sure is possible or not, hoping someone here can advise. I have a worksheet with a table (Power Query append output). This table appends information from 12 different worksheets (each a different location with entries by month and year). The result will then be filtered in the output table to show only the current month and year.

The only thing I'm not happy with is the January 2024 output (report/table) has' 2024' listed in A3:A15 and 'January' listed in B3:B15 - once for each location. Is there any possible way to use a cell outside the table to filter the month and date, so it doesn't need to be repetitively listed in each row? Trying to keep the report to only necessary columns. Month and year are necessary, but not 12 times! Not sure if this is even possible and I can't quite get on the right track with searching for answers. Thanks so much if anyone can point me in the right direction.

Kindest regards, Lauren
 

Attachments

  • PQoutput.png
    PQoutput.png
    3.3 KB · Views: 19

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Matt thank you so much, apologies for the late reply. I will try this out tomorrow and let you know how I go,many thanks :)
 
Upvote 0
Hi Matt,

Ok I've just checked out your article, which is really great, thank you, those killer tips are worth saving, for sure. I might not be experienced enough to get my head around this yet but I'm thinking that article looks like doing the opposite of what I need perhaps? My current PQ output has the year/month in columns and am looking for an option for a Senior Manager to be able to filter a report on the current month/year, with a desired outcome of the year/month being called once at the top of the report (via dropdown, manual entry or ???) without the user having to view the two columns with repeated dates (see attached image). I don't know if this is possible or not, or whether it's something that would need doing in the worksheet, or in PQ. Does this make sense? So basically, is there a way to filter PQ by desired year/month without user having to use the Year/Month column filters in the table, so I can hide those colums as they only need that info once, not a dozen times. I may not be explaining this very well. Hopefully the screenshot will help. I'm really keen to learn about PQ so any advice is useful, even if that advice is 'it's beyond your skillset at the moment'. I live in hope there is a solution. Please let me know if I've misunderstood the intent of your article and I'll revisit it. Thanks Matt :)
 

Attachments

  • PQexample.png
    PQexample.png
    109.4 KB · Views: 7
Upvote 0
Oh ok Matt, I think I see what you're suggesting. Just revisted your post and I'm making some headway now. Would this still work reliably if I deleted the Year and month columns from PQ so they don't show in the report, or is that pushing it?

After a bit more studying today, the process is starting to make a little more sense. Thank you so much! If you are able to let me know re the Year and Month columns in PQ, that would be awesome. I'm just heading out of town for 4 days and won't be able to revisit this until I return.

Many thanks, not working faultessly yet, but I'm getting there :)
 
Upvote 0
Hi Matt, FYI got it going after a little trial and error, many thanks :)
Sounds like you solved it using information from the link in Matt's post so I have changed the marked solution to that post as the post #5 that you had marked certainly doesn't contain the solution. :)
In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top