Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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-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"
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |