Power Query - filter based on cell outside table

[Larry of Oz]

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

 

[Matt Allington]

I think the technique I cover here will help you. Convert a Cell Value into a Column with Power Query

 

[Larry of Oz]

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

 

[Larry of Oz]

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

 

[Larry of Oz]

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

 

[Larry of Oz]

Hi Matt, FYI got it going after a little trial and error, many thanks

 

[Peter_SSs]

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.