none
Relevance of cyclic reference warning? RRS feed

  • Question

  • Hi there,

    I'm using a technique that works fine / delivers the expected results, but when checking the (intermediate) results of the steps within the query, there are either error-messages with cyclic references or functions with simple warnings about cyclic references.

    As I want to roll out this technique on a larger scale I'd like to check if the stable and correct output I'm experiencing so far can be further expected or if there is some risk in it:

    let
        MyFunctionLibrary = #table({"Name", "FunctionCode"}, {{"MyList", "{1,2,3,4,5}"}, {"NumberToGet", "1"}, {"GetNthNumber", "List.Reverse(f[MyList]){f[NumberToGet]}"}, {"GetNthNumberVar", "(NumberToGet) => let ntg = List.Reverse(f[MyList]){NumberToGet} in ntg"}}),
        Evaluate = Table.AddColumn(MyFunctionLibrary, "Value", each Expression.Evaluate([FunctionCode], Record.RemoveFields(#shared, {"Sql.Database", "Sql.Databases"}))),
        f = Record.FromTable(Evaluate)
    in
        f

    This returns a record in the format of #shared that contains my custom functions who then can be easily referenced throughout my workbook almost as if they were built-in-functions.

    That works fine, but when I check the steps there are either error-messages with cyclic-reference-warnings:

    Error message

    or the function is shown, but when checking it, also returns the warning:

    Function

    When calling #shared in a separate query, all turns out fine - so to me it looks as if the cyclic reference is only an issue during the creation of the record with the custom functions, but I'd like to get a confirmation on that. (n.B: It is important to be able to reference other custom functions within a custom functions, so the probable reason cannot be eliminated)

    Still struggling to understand how to use Expression.Evaluate properly: Is the risk of code injection now eliminated as I have excluded the data access-commands from the #shared-environment in step "Evaluate"? Are there other sources than SQL DB that are prone to that risk and should therefore be excluded as well?

    How about the warning this "not playing nicely with the static analysis" that Curt Hagenlocher mentioned in the comments here: https://blog.crossjoin.co.uk/2014/02/04/loading-power-query-m-code-from-text-files/. What kind of problems are to be expected here?

    Link to file: https://onedrive.live.com/edit.aspx?cid=de165ddf5d02daff&page=view&resid=DE165DDF5D02DAFF!75460&parId=DE165DDF5D02DAFF!107&app=Excel

    Really appreciate your help on this!


    Imke Feldmann TheBIccountant.com



    Tuesday, July 12, 2016 8:28 AM
    Moderator

Answers

  • Hi Imke. Sorry for the delay. After a bit of internal discussion, we've concluded:

    • The errors you're seeing when perusing the steps are likely due to a bug
    • However, this bug is not likely to be fixed anytime soon
    • As long as you don't get any errors when actually invoking the custom functions, your approach should be fine and you can ignore these errors

    Hope that helps.

    Ehren


    Thursday, July 21, 2016 6:16 PM
    Owner

All replies

  • Hi Imke. The issue appears to be that the M code you're dynamically evaluating inside of the "f" query contains references to the "f" query. Is this necessary?

    Ehren

    Tuesday, July 12, 2016 4:53 PM
    Owner
  • Something like this would be much simpler. It builds up an environment record (which is what #shared is) by iterating over the list of function definitions, passing the previous row's environment record result into the Expression.Evaluate for the current row. The only limitation is that the functions you're defining must be listed in dependency order (e.g. in your example GetNthNumberVar must be declared after MyList).

    let
        MyFunctionLibrary = #table({"Name", "Code"}, {{"MyList", "{1,2,3,4,5}"}, {"NumberToGet", "1"}, {"GetNthNumber", "List.Reverse(MyList){NumberToGet}"}, {"GetNthNumberVar", "(NumberToGet) => let ntg = List.Reverse(MyList){NumberToGet} in ntg"}}),
        Evaluate = List.Accumulate(Table.ToRecords(MyFunctionLibrary), [List.Reverse = List.Reverse], (result, row) => result & Record.FromList({Expression.Evaluate(row[Code], result)}, {row[Name]}))
    in
        Evaluate

    Ehren

    Tuesday, July 12, 2016 5:12 PM
    Owner
  • Hi Ehren,

    thanks - but that doesn't look to what I'm after. This was meant as a POC for large libraries that can be read in as one text block and executed/referenced easily. Basically doing what a lot of people already suggested: Load custom library packages like in R.

    So it doesn't seem feasible to do the sorting and the referencing/lookup of some functions in the Evaluate step. I would expect that to slow the process down considerably - or am I wrong here?


    Imke Feldmann TheBIccountant.com

    Tuesday, July 12, 2016 5:52 PM
    Moderator
  • Would the text block you're reading in correspond to a single query, or multiple queries?

    Ehren

    Tuesday, July 12, 2016 6:22 PM
    Owner
  • There will be multiple ways to create the text block, depending on how people prefer to use this technique (from local text files or tables or even directly from GitHub). But this consolidation process will always end in the same syntax: The (custom) functions who reference other custom functions call them with the syntax of the record to build: f[ReferencedCustomFunction].

    Imke Feldmann TheBIccountant.com

    Tuesday, July 12, 2016 6:42 PM
    Moderator
  • In that case, my suggested solution will indeed not work. Do you get the cyclic reference errors only when browsing the steps, or also when invoking the newly created functions?

    Ehren

    Wednesday, July 13, 2016 11:36 PM
    Owner
  • Thanks - only when browsing the steps warnings are shown. When this record is finished and called by #shared, all warnings go away and when the functions in it are invoked all works fine as well.

    I just wanted to ask that/why this outcome can further be expected and a solution like that can safely be rolled out.

    Thanks again - Imke 


    Imke Feldmann TheBIccountant.com

    Thursday, July 14, 2016 6:27 AM
    Moderator
  • Hi Imke,

    Instead of creating a table, why now create a record instead? This way, you could write the record values  without having to use quotes and struggle with Expression.Evaluate, e.g.

    let
      MyFunctionLibrary = [MyList = {1,2,3,4,5}, NumberToGet = "1", 
                        MyFunction = 
                          let 
                            func = (number as number) as number => number,
                            documentation = [
                              Documentation.Name = "MyFunction",
                              Documentation.Description = "Returns a shiny new number.",
                              Documentation.LongDescription = "Returns a magical, shiny, brand-new number.",
                              Documentation.Category = "Number",
                              Documentation.Examples = {[Description = "The first example.", Code = "MyFunction(123)", Result = "123"]}
                            ]
                        in
                          Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))]
    in
        MyFunctionLibrary
    Admittedly, the custom function library is going to be a huge record if you have tens and tens of custom functions. :)
      

    Saturday, July 16, 2016 4:37 PM
  • Hi Imke,

    For importing code from a text file, VBA might be a solution, as long as the start and end of a code block can be clearly delineated. I'd use the FileSystemObject library, because it contains the useful TextStream object (which is easier to work with than VBA's File I/O). As the file is read, and a code block identified, one can use Workbook.Queries.Add to construct the corresponding M query in the workbook. One downside of this solution is that it would work only in Excel 2016.

    I'm not convinced that Expression.Evaluate will get you what you want, given the amount of time you've spent working with this function unsuccessfully (even with "insider" help - so near and yet so far). I'd love to be wrong, however. 

    Saturday, July 16, 2016 8:34 PM
  • Hi Imke. Sorry for the delay. After a bit of internal discussion, we've concluded:

    • The errors you're seeing when perusing the steps are likely due to a bug
    • However, this bug is not likely to be fixed anytime soon
    • As long as you don't get any errors when actually invoking the custom functions, your approach should be fine and you can ignore these errors

    Hope that helps.

    Ehren


    Thursday, July 21, 2016 6:16 PM
    Owner
  • Thanks Ehren,

    have continued to use it and didn't experience any issues so far.


    Imke Feldmann TheBIccountant.com

    Thursday, July 21, 2016 7:54 PM
    Moderator