power query | parameter query to filter data by dates (year) from google bigquery database

[hood2media]

hi,

i get my data for period beginning 01 January 2015 from google bigquery as follows:

Source = Value.NativeQuery(GoogleBigQuery.Database()
{[Name="abc-stock"]}[Data],
"select * from abc-stock.uk.uk
where (ORIGIN='LAX' or DEST='LAX')",
null,
[EnableFolding=true])

note:
i have filtered the output for ORIGIN or DEST to be 'LAX'.

the data only date value is in the column DATE.

i'd like to create a parameter query that allows me to filter data for following condition examples:

1. year is > 2017

2. year is between 2015-2018

i'd appreciate any help to show me how to parameter query to filter for those condition.

tks & krgds, -nik

p.s.

separately, i have seen how power query in ms excel uses parameter table but i'm not sure if such method is available in ms power bi too.

[hood2media]

thanks for your input, @lbendlin. 

as this (sql in power bi) is still new to me, can u kindly show me an example (a dummy case will do)?

tks & kind rgds, -nik

[lbendlin · Accepted solution]

This blog article describes the approach well, including a good M code example.

Passing Any Arbitrary Value From A Power BI Report To A Dynamic M Parameter (crossjoin.co.uk)

[lbendlin]

Set the parameter type to List of values.  You will need to cover both scenarios (single value provided or multiple values provided) and change your SQL statement accordingly.  Use Value.Is  to tell one from the other.