cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smpa01 Established Member
Established Member

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

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"

Capture.JPG

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.

1 ACCEPTED SOLUTION

Accepted Solutions
smpa01 Established Member
Established Member

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

@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

11/20/2016 0:00
22/20/2016 0:00
33/20/2016 0:00
44/20/2016 0:00
55/20/2016 0:00
66/20/2016 0:00
77/20/2016 0:00
88/20/2016 0:00
99/20/2016 0:00
1010/20/2016 0:00
1111/20/2016 0:00
1212/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"

 

2 REPLIES 2
Community Support Team
Community Support Team

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

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

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
smpa01 Established Member
Established Member

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

@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

11/20/2016 0:00
22/20/2016 0:00
33/20/2016 0:00
44/20/2016 0:00
55/20/2016 0:00
66/20/2016 0:00
77/20/2016 0:00
88/20/2016 0:00
99/20/2016 0:00
1010/20/2016 0:00
1111/20/2016 0:00
1212/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"

 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 289 members 3,027 guests
Please welcome our newest community members: