Power Query - Filter Rows by Multiple Queries

[depple]

Hi,

First of all: I am a total newbie in Power Query, having just taken my first self-learned steps in understanding it. I have ordered books that have yet not arrived, so hopefully I will understand more then. Until so happens, please be gentle and expect limited knowledge from me on what I am actually doing :

I have this filter in my query:

= Table.SelectRows(dbo_Data, each ([EAC] = "ABC") and ([ReportId] = 4))

It appears to be working fine just. However, I would like to replace the "4"-criteria with the maximum value in ReportId, so that that all old reports are filtered away.

Is this something that could be done within this formula, or do I need to build something more?

 

[anvg]

Hi

//define max ReportId value
maxId = List.Max(dbo_Data[ReportId]),
filtered = Table.SelectRows(dbo_Data, each ([EAC] = "ABC") and ([ReportId] = maxId))

Happy New Year!
Regards,

 

[depple]

anvg,

Happy New Year!

Your solution worked perfectly, thank you!

In another situation I might wish to get the ReportId (or similar) from the spreadsheet, for instance through a user dropdown menu. Is there a way of fetching the ReportId filter value from the spreadsheet?

Have a nice day.

Regards,

 

[anvg]

Hi
Please, try to learn my example Zippyshare.com - Example.xlsx. It is simplified of Building a Parameter Table for Power QueryThe Ken Puls (Excelguru) Blog. On Report sheet a user can select date of a report from dbo_Data and refresh with "Refresh all" button on "Data" tab after. A source of that date list is a query table result on Auxiliary sheet.
Regards,

 

[depple]

anvg,

Thank you.

It appears to be a little above my current level, but I will certainly look closer into it when I get a little further in the learning process.

Regards,

 

[ImkeF]

If you just want to pass just 1 cell as a filter/parameter to Power Query, there is actually a very quick method to it:

Check your cell -> give it a name -> check your cell -> Power Query -> Excel Data -> From Table

This will autocreate a query with 3 steps. Delete the last 2 steps, check the (only) cell in that table - right click -> drill down.

This creates a query with an output as text value. You can use this like any other (fix) parameter in your queries/filters.