none
How to properly use Expression.Evaluate? RRS feed

  • Question

  • In order to filter SQL-sources by external tables (sitting in Excel), we could use Expression.Evaluate according to this method (http://www.thebiccountant.com/2016/03/08/select-rows-that-have-no-empty-fields-using-expression-evaluate-in-power-bi-and-power-query/) to create the native query statements. But the only way I found to make it work is to use  #shared as the environment. I’ve heard that this is dangerous, but didn’t understand why and am not aware of any alternative.

    Could someone please help me out here? :

    Can I recommend this solution to a customer who would use it only within an Excel-solution?

    Are there dangers attached to it that I should be aware of / give a warning to the customer?

    How/where can I find out how to use Expression.Evaluate properly?

    Or are there better methods to filter SQL-sources by external tables?

    ... assumption is that folding shall be maintained.


    Imke Feldmann TheBIccountant.com


    Friday, April 29, 2016 8:56 PM
    Moderator

Answers

  • Hi Imke,

    Expression.Constant and Expression.Identifier are useful when building up M expressions. Expression.Constant handles things like quoting of text values (and escaping nested quotes). Expression.Identifier handles things like escaping of identifiers via #"this syntax" when necessary.

    Injection is a risk whenever users are entering values that will be inserted into the text being evaluated (whether that text be M or SQL or some other language), and when you want to restrict what they can do. (If a user already has full control over the SQL database, or already has the ability to author new M via Power Query, for example, then injection would only allow them to do something they can already do via other means.)

    Ehren

    Monday, May 9, 2016 5:20 PM
    Owner

All replies

  • Are you wanting to generate native SQL queries yourself (which will break subsequent folding done by the M engine), or generate M which can be folded?

    If you could provide a brief sample of a SQL table, and the data contained in Excel you want to use to filter it, that would be helpful.

    To call Expression.Evaluate without passing #shared, you can provide a record that contains only the identifiers you want to be able to reference inside your dynamic M text. For example:

    = Expression.Evaluate("Text.StartsWith(""abc"", ""a"")", [Text.StartsWith = Text.StartsWith])

    Ehren

    Tuesday, May 3, 2016 5:02 PM
    Owner
  • Thank you.

    I'm using this statement in order to provide the advanced search functions from Excel to be applied to a SQL-datasource instead:

    SqlQuery = Expression.Evaluate("

    Sql.Database(ParaServerName, ParaDB,

    [Query=""Select * from "&ParaTable&" where "&QueryString&"])

    ", [ParaServerName=ParaServerName, ParaDB=ParaDB, Sql.Database=Sql.Database])

    So the users fill in an Excel-table with their search criteria, Power Query then dynamically creates the text for the SQL-query that will be passed on to the SQL-query that Expression.Evaluate then executes.

    I've managed to do without #shared like you said, but would like to know if/how to use Expression.Constant and/or Expression.Identifier here. Also what wonders me is why I need to declare "ParaServerName" and "ParaDB" in the environment, but not "ParaTable" and "QueryString".

    Also I'm not fully aware of the dangers of injection attacks when using all this in Excel only - doesn't his only apply when using it in a web-environment (Excel-Services or PBI service)?

    Thanks a lot!


    Imke Feldmann TheBIccountant.com



    Sunday, May 8, 2016 4:38 PM
    Moderator
  • Hi Imke,

    Wouldn't it be simpler to create a native query custom function, then add a custom column to the user table where each value in the "Search Criteria" column is passed as an argument to the custom function?

    Sunday, May 8, 2016 4:57 PM
  • Hi Colin,

    Not sure if I understood you correctly. But this is a solution for a client who wants to use the Excel-wildcard-characters, so there's quite some transformation-work to be done (also because the number of rows and the selection of the columns shall be variable). So this is the full query (with some sample-data provided):

    let
        ParaServerName = "MyServer",
        ParaDB = "AdventureWorksDW2012 ",
        ParaTable = "DimCustomer",

        tblOperators = Table.FromRows({{">"}, {">="}, {"<"}, {"<="}, {"="}}, {"Operator"}),
        tblFilter = Table.FromRows({{"Jen*", "", ">=  50000"}, {"J?hn", "M", ""}}, {"FirstName", "MaritalStatus", "YearlyIncome"}),
       
        Source =tblFilter ,
        #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
        UnpivotOther = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
        FilterValues = Table.SelectRows(UnpivotOther, each ([Value] <> "")),
        Index2 = Table.AddIndexColumn(FilterValues, "Index2", 0, 1),
        MergeOperators = Table.AddColumn(Index2, "Operators", each tblOperators),
        ExpandOperators = Table.ExpandTableColumn(MergeOperators, "Operators", {"Operator", "Length"}, {"Operator", "Length"}),
        CheckOperators = Table.AddColumn(ExpandOperators, "Operators", each Text.StartsWith([Value], [Operator])),
        FilterOperators = Table.SelectRows(CheckOperators, each ([Operators] = true)),
        ReplaceOperators = Table.ReplaceValue(FilterOperators,each [Operator],"",Replacer.ReplaceText,{"Value"}),
        MergeBack = Table.NestedJoin(Index2,{"Index", "Index2"},ReplaceOperators,{"Index", "Index2"},"NewColumn",JoinKind.LeftOuter),
        ExpandNewOperators = Table.ExpandTableColumn(MergeBack, "NewColumn", {"Value", "Operator"}, {"Value.1", "Operator"}),
        ReplWildcard1 = Table.ReplaceValue(ExpandNewOperators,"*","%",Replacer.ReplaceText,{"Value"}),
        ReplWildcard2 = Table.ReplaceValue(ReplWildcard1,"?","_",Replacer.ReplaceText,{"Value"}),
        CreateQueryString1 = Table.AddColumn(ReplWildcard2, "Custom", each if [Operator]=null then "["&[Attribute]&"] like '"&[Value]&"'" else "["&[Attribute]&"] "& [Operator]&[Value.1]),
        QueryString = Text.Combine(Table.Group(CreateQueryString1, {"Index"}, {{"Count", each Text.Combine(_[Custom], " and "), type table}})[Count], " or ")&"""",
        SqlQuery = Expression.Evaluate("Sql.Database(ParaServerName, ParaDB, [Query=""Select * from "&ParaTable&" where "&QueryString&"])", [ParaServerName=ParaServerName, ParaDB=ParaDB, Sql.Database=Sql.Database])
    in
        SqlQuery

    How would this translate to your proposal?


    Imke Feldmann TheBIccountant.com



    Sunday, May 8, 2016 5:07 PM
    Moderator
  • Can you:

    1) Construct a custom column named QueryString (for example), which holds the modified version of the user  query strings

    2) Add another custom column named QueryResult (for example), where the formula is Table.AddColumn(<PreviousStepName>, "QueryResult", each ExecuteQuery([QueryString]))

    ExecuteQuery being a separate custom function:

    (serverName as text, dbName as text, tableName as text, queryString as text) =>
    "Sql.Database(serverName, dbName, [Query=""Select * from "&tableName&" where "&queryString&"])"

    Sunday, May 8, 2016 6:10 PM
  • Thank you. Tried some variants here (as the function you've provided requires 4 arguments, but the execution-statement only takes in 1):

    Your function "ExecuteQuery" returns a string. So I would still need to pass it through an "Expression.Evaluate"-command in order to retrieve the results from the SQL-server. Or did I do sth wrong?


    Imke Feldmann TheBIccountant.com

    Sunday, May 8, 2016 7:44 PM
    Moderator
  • There we go. No Expression.Evaluate needed @ all (thanks to Ken Puls: )

    let
        ParaServerName = "YourServer",
        ParaDB = "AdventureWorksDW2012 ",
        ParaTable = "DimCustomer",
    
        tblOperators = Table.FromRows({{">"}, {">="}, {"<"}, {"<="}, {"="}}, {"Operator"}),
        tblFilter = Table.FromRows({{"Jen*", "", ">=  50000"}, {"J?hn", "M", ""}}, {"FirstName", "MaritalStatus", "YearlyIncome"}),
        
        Source =tblFilter ,
        #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
        UnpivotOther = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
        FilterValues = Table.SelectRows(UnpivotOther, each ([Value] <> "")),
        Index2 = Table.AddIndexColumn(FilterValues, "Index2", 0, 1),
        MergeOperators = Table.AddColumn(Index2, "Operators", each tblOperators),
        ExpandOperators = Table.ExpandTableColumn(MergeOperators, "Operators", {"Operator", "Length"}, {"Operator", "Length"}),
        CheckOperators = Table.AddColumn(ExpandOperators, "Operators", each Text.StartsWith([Value], [Operator])),
        FilterOperators = Table.SelectRows(CheckOperators, each ([Operators] = true)),
        ReplaceOperators = Table.ReplaceValue(FilterOperators,each [Operator],"",Replacer.ReplaceText,{"Value"}),
        MergeBack = Table.NestedJoin(Index2,{"Index", "Index2"},ReplaceOperators,{"Index", "Index2"},"NewColumn",JoinKind.LeftOuter),
        ExpandNewOperators = Table.ExpandTableColumn(MergeBack, "NewColumn", {"Value", "Operator"}, {"Value.1", "Operator"}),
        ReplWildcard1 = Table.ReplaceValue(ExpandNewOperators,"*","%",Replacer.ReplaceText,{"Value"}),
        ReplWildcard2 = Table.ReplaceValue(ReplWildcard1,"?","_",Replacer.ReplaceText,{"Value"}),
        CreateQueryString1 = Table.AddColumn(ReplWildcard2, "Custom", each if [Operator]=null then "["&[Attribute]&"] like '"&[Value]&"'" else "["&[Attribute]&"] "& [Operator]&[Value.1]),
        QueryString = "Select * FROM "&ParaTable&" WHERE "&Text.Combine(Table.Group(CreateQueryString1, {"Index"}, {{"Count", each Text.Combine(_[Custom], " and "), type table}})[Count], " or "),
        SqlQuery = Sql.Database(ParaServerName, ParaDB, [Query=QueryString])
    in
        SqlQuery
    Key is to create the whole Query-string as one string without using parameters in it during the call.


    Imke Feldmann TheBIccountant.com

    Monday, May 9, 2016 5:41 AM
    Moderator
  • This works only on sources who take in strings like that. So it wouldn't work on csv-files i.e.

    Although not required at the moment, I would be very interested to know how to use the accompagning commands to Expression.Evaluate properly. So if anyone feels in the mood to explain, I'd be very grateful.

    Will keep this thread open for 1-2 days before closing.


    Imke Feldmann TheBIccountant.com

    Monday, May 9, 2016 5:58 AM
    Moderator
  • Hi Imke,

    Expression.Constant and Expression.Identifier are useful when building up M expressions. Expression.Constant handles things like quoting of text values (and escaping nested quotes). Expression.Identifier handles things like escaping of identifiers via #"this syntax" when necessary.

    Injection is a risk whenever users are entering values that will be inserted into the text being evaluated (whether that text be M or SQL or some other language), and when you want to restrict what they can do. (If a user already has full control over the SQL database, or already has the ability to author new M via Power Query, for example, then injection would only allow them to do something they can already do via other means.)

    Ehren

    Monday, May 9, 2016 5:20 PM
    Owner