Using a variable as an operator in a Power BI query filter

[vgeldbr]

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))

[edhans]

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.

[Anonymous]

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

[vgeldbr]

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?

[Anonymous]

Sorry, please change Table to myTable in this line:

Source = Table, //Should be Source = myTable,

This is the modified code from your example, but I can't test it, let me know if it does not work:

let 
    //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 = 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 List.Contains(PortfolioFilterList, [Portfolio]) and fComparer(SubPortfolioFilterList{0}, [SubPortfolio], "<>")),
    #"Filtered Rows Against List" = Table.SelectRows(dbo_VW_PPM_ExportFinancial_FY20, each List.Contains(PortfolioFilterList, [Portfolio]) and fComparerList(SubPortfolioFilterList, [SubPortfolio], "<>"))

in #"Filtered Rows Against List"

By what I can see in your original post you want #"Filtered Rows Against List" as an output (#"Filtered Rows" only checks it against the first item in the SubPortfolioFilterList - I put it as an example if you want to compare it the way in the original post:

each List.Contains(PortfolioFilterList, [Portfolio]) and (fComparer(SubPortfolioFilterList{0}, [SubPortfolio], "<>") and fComparer(SubPortfolioFilterList{1}, [SubPortfolio], "<>") and fComparer(SubPortfolioFilterList{2}, [SubPortfolio], "<>") and fComparer(SubPortfolioFilterList{3}, [SubPortfolio], "<>"))

Kind regards,

JB

@vgeldbr  

[vgeldbr]

This is fantastic. One last piece of logic I'm missing. I have a parameter (SubPortfolio Filter Type) which can be TRUE or FALSE. This allows the user to specify if subportfolio filter includes or excludes the list of SupPortfolios. My code is below now but I can't figure where to set this as your example is assuming always <>.

let
    //Variables - Portfolio:
    PortfolioFilterList = Text.Split(#"Portfolio Filter", ";"),
    //Variables - SubPortfolio:
    SubPortfolioFilterList = Text.Split(#"SubPortfolio Filter", ";"),
    //Variables - filter type:
    SubPortfolioFilterType = if #"SubPortfolio Filter Type" = false then "<>" else "=",
   
    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 = Sql.Databases("DERUSVMPITRSQ01.EY.NET\INST1"),
    IT_ITRDB_PRD = Source{[Name="IT_ITRDB_PRD"]}[Data],
    dbo_VW_PPM_ExportFinancial_FY20 = IT_ITRDB_PRD{[Schema="dbo",Item="VW_PPM_ExportFinancial_FY20"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_VW_PPM_ExportFinancial_FY20, each List.Contains(PortfolioFilterList, [Portfolio]) and fComparer(SubPortfolioFilterList{0}, [SubPortfolio], "<>")),
    #"Filtered Rows Against List" = Table.SelectRows(dbo_VW_PPM_ExportFinancial_FY20, each List.Contains(PortfolioFilterList, [Portfolio]) and fComparerList(SubPortfolioFilterList, [SubPortfolio], "<>"))

in #"Filtered Rows Against List"

@Anonymous 

[Anonymous · Accepted solution]

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 "&lt;&gt;"))

Kind regards
JB

[vgeldbr]

@Anonymous  thanks for all of your help. You've provided me with all the pieces I needed to achieve what I wanted!