Forum Discussion
Filter table based on other table
New to all this ...
How can I filter import of tableA from Access, where field [Month] is equal/less than Tableb[CurMonth], a field we manually update when new month's data is ready to load on BI?
Thank you
thanks austinsense :-)
filter equal is easiest done by merging with JoinKind.Inner as described in these articles:
http://www.thebiccountant.com/2015/08/18/use-timeline-or-slicers-to-filter-your-power-query-import/
filter < is a bit different: There you have to use parameters. To do so you start filtering your table in the desired way by putting in a hardcoded value. After that you edit the resulting M-code and replace the hardcoded value by the parameter.
The parameter can be retrieved from a table by it's coordinates:
queryname[Columnname]{Rownumber-1}
So say your parameter sits in the first row the expression would be: Tableb[CurrMonth]{0}
Rownumber is zero because M starts to count with 0 (for the 1st row)
So this is how the parametrized filter would look like:
= Table.SelectRows(Source, each [Month] < Tableb[CurrMonth]{0})
6 Replies
Create a calculcated column which checks for the "size" of the month and compare. If comparison is true, parse "Yes", otherwise "No". Then use this help-column for filtering via filter pane or slicer.
ImkeF do you have some query magic for this guy?
thanks austinsense :-)
filter equal is easiest done by merging with JoinKind.Inner as described in these articles:
http://www.thebiccountant.com/2015/08/18/use-timeline-or-slicers-to-filter-your-power-query-import/
filter < is a bit different: There you have to use parameters. To do so you start filtering your table in the desired way by putting in a hardcoded value. After that you edit the resulting M-code and replace the hardcoded value by the parameter.
The parameter can be retrieved from a table by it's coordinates:
queryname[Columnname]{Rownumber-1}
So say your parameter sits in the first row the expression would be: Tableb[CurrMonth]{0}
Rownumber is zero because M starts to count with 0 (for the 1st row)
So this is how the parametrized filter would look like:
= Table.SelectRows(Source, each [Month] < Tableb[CurrMonth]{0})Thank you! The imported data table now only shows up to month specified in TableB[mnt]. But, the reports 'working of' this table still include later data, i.e. I though this would restrict all reports based on that table to be limited to that?
I.e. used: #"Up to Month" = Table.SelectRows(_CSI, each [Month] <= DBmnt[Mnt]{0})
