Forum Discussion
Using a variable as an operator in a Power BI query filter
This may not be very elegant but would appreciate some help to find a solution.
I have set up a Parameter (SubPortfolio Filter) to filter a query based on the values in a column. I have another Parameter (SubPortfolio Filter Type) that indicates if the filter is to exclude or include the values. Parameters don't allow multiple values so I have to parse the individual values out and then use them in variables.
I would like to set the query so that I can use a the SubPortfolio Filter Type parameter as a variable that uses "<>" or "=" depending on the parameter. Here is an example of the code. When I use the variable name I get an error.
let
//Variables - Portfolio:
PortfolioFilterList = Text.Split(#"Portfolio Filter", ";"),
PortfolioFilter1 = Lines.ToText(List.Range(PortfolioFilterList, 0, 1),""),
PortfolioFilter2 = Lines.ToText(List.Range(PortfolioFilterList, 1, 1),""),
//Variables - SubPortfolio:
SubPortfolioFilterList = Text.Split(#"SubPortfolio Filter", ";"),
SubPortfolioFilter1 = Lines.ToText(List.Range(SubPortfolioFilterList, 0, 1),""),
SubPortfolioFilter2 = Lines.ToText(List.Range(SubPortfolioFilterList, 1, 1),""),
SubPortfolioFilter3 = Lines.ToText(List.Range(SubPortfolioFilterList, 2, 1),""),
SubPortfolioFilter4 = Lines.ToText(List.Range(SubPortfolioFilterList, 3, 1),""),
SubPortfolioFilterType = if #"SubPortfolio Filter Type" = false then "<>" else "=",
Source = Sql.Databases("SQLServerName\INST1"),
IT_ITRDB_PRD = Source{[Name="xxxPRD"]}[Data],
dbo_VW_PPM_ExportFinancial_FY20 = xxxPRD{[Schema="dbo",Item="VW_PPM_ExportFinancial_FY20"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_VW_PPM_ExportFinancial_FY20, each ([Portfolio] = PortfolioFilter1 or [Portfolio] = PortfolioFilter2) and ([SubPortfolio] <> SubPortfolioFilter1 and
[SubPortfolio] <> SubPortfolioFilter2 and [SubPortfolio] <> SubPortfolioFilter3 and [SubPortfolio] <> SubPortfolioFilter4))
in
#"Filtered Rows"
Fails with error Expression.SyntaxError: Token RightParen expected.
#"Filtered Rows" = Table.SelectRows(dbo_VW_PPM_ExportFinancial_FY20, each ([Portfolio] = PortfolioFilter1 or [Portfolio] = PortfolioFilter2) and ([SubPortfolio] SubPortfolioFilterType SubPortfolioFilter1 and
[SubPortfolio] SubPortfolioFilterType SubPortfolioFilter2 and [SubPortfolio] SubPortfolioFilterType SubPortfolioFilter3 and [SubPortfolio] SubPortfolioFilterType SubPortfolioFilter4))
- Hi vgeldbr
This should work
Table.SelectRows(dbo_VW_PPM_ExportFinancial_FY20, each List.Contains(PortfolioFilterList, [Portfolio]) and fComparerList(SubPortfolioFilterList, [SubPortfolio], if SubPortfolioFilterType then "=" else "<>"))
Kind regards
JB
7 Replies
I could be wrong, but I don't think you can replace operators with variables. In other words assigning the = sign to varEqualSign won't let each if [Weight] varEqualSign 10 then 1 else 0 work.
You'd need to multiple IF statements so if it is false, then do and if [blah] <> 1 then something else if [blah] = 1 then something.
If you can use a variable for the operator, I'd love to see the syntax for that.Hi all,
The M language syntax doesn't support something like Parameter1 Comparer Parameter2, but it perfectly supports Comparer(Parameter1, Parameter2). In this case, the Comparer function can be overloaded at execution time.
This makes possible to do something like this:
let #"Portfolio Filter" = "a; b; c", #"SubPortfolio Filter" = "aa; ab; ba; cb", #"SubPortfolio Filter Type" = false, myTable = let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUpMVIrVgTCTU8DMZBAzSSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Portfolio = _t, SubPortfolio = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Portfolio", type text}, {"SubPortfolio", type text}}) in #"Changed Type", //Variables - Portfolio: PortfolioFilterList = Text.Split(#"Portfolio Filter", ";"), //Variables - SubPortfolio: SubPortfolioFilterList = Text.Split(#"SubPortfolio Filter", ";"), fComparer = (param1 as any, param2 as any, c_type as text)=> if c_type = "=" then param1 = param2 else if c_type = "<" then param1 < param2 else if c_type = ">" then param1 > param2 else if c_type = "<=" then param1 <= param2 else if c_type = ">=" then param1 >= param2 else if c_type = "<>" then param1 <> param2 else null, fComparerList = (param1 as list, param2 as any, c_type as text)=> if c_type = "=" then List.Contains(param1,param2) else if c_type = "<>" then not List.Contains(param1,param2) else null, Source = Table, #"Filtered Rows" = Table.SelectRows(Source, each List.Contains(PortfolioFilterList, [Portfolio]) and fComparer(SubPortfolioFilterList{0}, [SubPortfolio], "<>")), #"Filtered Rows Against List" = Table.SelectRows(Source, each List.Contains(PortfolioFilterList, [Portfolio]) and fComparerList(SubPortfolioFilterList, [SubPortfolio], "<>")) in #"Filtered Rows Against List"Kind regards,
JB
Thanks Anonymous , this looks very promising. Maybe it's just too late on a Sunday evening but I'm not getting how to integrate this with my code to reference my SQL table. When I use your code there are no syntax errors but when I save I get:
Expression.Error: The import Table matches no exports. Did you miss a module reference?
