Forum Discussion

vgeldbr's avatar
vgeldbr
Icon for Helper IV rankHelper IV
6 years ago
Solved

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 P...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago
    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
edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    vgeldbr
    Icon for Helper IV rankHelper IV
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        vgeldbr
        Icon for Helper IV rankHelper IV
        6 years ago

        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