POWER Query SQL Statement Dynamic Filtering based on a different query value

[smpa01]

Hi,

I am building a query (Query1) by connecting to a SQL Server Table. To avoid having all the rows I have done a custom SQL Statement to filter while connceting to SQL server as following.

select [$Table].[CreatedDate] as [CreatedDate],
       [$Table].[Site ID] as [Site ID]       
   from [dbo].[vw_xxxxxxx] as [$Table]
   WHERE ([CreatedDate] >= N'2016-08-20 00:00:00') 

The CreatedDate needs to be filtered based on a value derived from a different query (Querry2). For example, let's suppose the query is based on the following code which gives me the following value

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Created on", type date}})
in
    #"Changed Type"

Is there a way to pass on the value derived in Query2 to the filter in Query1 on CreatedDate for the following portion

WHERE ([CreatedDate] >= N'2016-08-20 00:00:00') 

Thank you in advance.

[smpa01 · Accepted solution]

@v-lili6-msftthanks I managed to set up the dynamic filter based on the parameter

Magic

let
    Source = (P1 as text) => let
    Source = Sql.Database("Example", "Example1", [Query="select CURRENT_TIMESTAMP,       #(lf)       [$Table].[CreatedDate] as [CreatedDate],#(lf)       [$Table].[Site ID] as [Site ID],#(lf)       [$Table].[Site Name] as [Site Name],#(lf)       [$Table].[Sub Case #] as [Sub Case #],#(lf)       [$Table].[Condition] as [Condition],#(lf)       [$Table].[Status] as [Status],#(lf)       [$Table].[Serv. Prov. Name] as [Serv. Prov. Name],#(lf)       [$Table].[Serv. Prov. Category] as [Serv. Prov. Category],#(lf)       [$Table].[Completion Date] as [Completion Date],#(lf)       [$Table].[Service Level] as [Service Level],#(lf)       [$Table].[Subcase Type] as [Subcase Type],#(lf)       [$Table].[Service Category] as [Service Category],#(lf)       [$Table].[Service Type] as [Service Type],#(lf)       [$Table].[Billable] as [Billable],#(lf)       [$Table].[NTE Amount] as [NTE Amount],#(lf)       [$Table].[Business Unit] as [Business Unit],#(lf)       [$Table].[State] as [State],#(lf)       [$Table].[--------------------------------Description--------------------------------] as [Description]#(lf)     from [dbo].[xxxx] as [$Table]#(lf)   WHERE [CreatedDate] >= '"&P1&"' AND NOT [Completion Date] IS NULL"])
in
    Source
in
    Source

Table4

IndexDate

1	1/20/2016 0:00
2	2/20/2016 0:00
3	3/20/2016 0:00
4	4/20/2016 0:00
5	5/20/2016 0:00
6	6/20/2016 0:00
7	7/20/2016 0:00
8	8/20/2016 0:00
9	9/20/2016 0:00
10	10/20/2016 0:00
11	11/20/2016 0:00
12	12/20/2016 0:00

  let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Date", type datetime}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Day" = Table.AddColumn(#"Inserted Month", "Day", each Date.Day([Date]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Day", "Custom", each if Text.Length(Text.From([Month]))<>2 then Text.PadStart(Text.From([Month]),2,"0") else [Month]),
    #"Inserted Merged Column" = Table.AddColumn(#"Added Custom", "Merged", each Text.Combine({Text.From([Year], "en-US"), Text.From([Custom], "en-US"), Text.From([Day], "en-US")}, "-"), type text),
    #"Added Custom1" = Table.AddColumn(#"Inserted Merged Column", "Custom.1", each "00:00:00"),
    #"Merged Columns" = Table.CombineColumns(#"Added Custom1",{"Merged", "Custom.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged.1"),
    #"Removed Other Columns" = Table.SelectColumns(#"Merged Columns",{"Index", "Merged.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Merged.1", "P1"}}),
    #"Added Custom2" = Table.AddColumn(#"Renamed Columns", "Custom", each DateTime.LocalNow()),
    #"Inserted Month1" = Table.AddColumn(#"Added Custom2", "Month", each Date.Month(Date.AddMonths(Date.From(DateTime.LocalNow()),-1)), Int64.Type),
    #"Added Custom3" = Table.AddColumn(#"Inserted Month1", "Custom.1", each if [Index]=[Month] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom3", each ([Custom.1] = 1)),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"P1"}),
    #"Invoked Custom Function" = Table.AddColumn(#"Removed Other Columns1", "Magic", each Magic([P1])),
    #"Expanded Magic" = Table.ExpandTableColumn(#"Invoked Custom Function", "Magic", {"", "CreatedDate", "Site ID", "Site Name", "Sub Case #", "Condition", "Status", "Serv. Prov. Name", "Serv. Prov. Category", "Completion Date", "Service Level", "Subcase Type", "Service Category", "Service Type", "Billable", "NTE Amount", "Business Unit", "State", "Description"}, {"Column1", "CreatedDate", "Site ID", "Site Name", "Sub Case #", "Condition", "Status", "Serv. Prov. Name", "Serv. Prov. Category", "Completion Date", "Service Level", "Subcase Type", "Service Category", "Service Type", "Billable", "NTE Amount", "Business Unit", "State", "Description"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Magic",{{"CreatedDate", Order.Ascending}})
in
    #"Sorted Rows"

[v-lili6-msft]

hi, @smpa01

     You can try to use Parameters in Power BI, you can add a parameter and set the Query2 as the values.

This is the tutorial for you to refer to

reference:https://www.mssqltips.com/sqlservertip/4475/using-parameters-in-power-bi/

Best Regards,

Lin