none
User-Defined Function w/ List Parameter RRS feed

  • Question

  • When I pass a list to a user defined function (shown in Excel above) I get a Choose Column button upon invocation to specify a table column rather than a field to provide a list reference.  Why is this?  Are lists not supported?  Is my parameter declaration incorrect?

    As an aside, I should mention that I did see a Chris Webb blog entry that describes the behavior that I show above.  He shows how this behavior can be used to good effect; however, I want to pass a "list of lists" as an argment and have the user-defined function process it as is.  Why can't I do that?
    Monday, January 15, 2018 6:19 PM

Answers

  • I submitted an Power BI UserVoice request to have the user-defined function invocation dialog changed. 

    Please Vote for this request if you agree that such a change is needed.


    • Marked as answer by Mark Weisman Wednesday, January 17, 2018 10:35 PM
    • Edited by Mark Weisman Wednesday, January 17, 2018 10:37 PM
    Wednesday, January 17, 2018 10:35 PM

All replies

  • Indeed, the User Interface isn't very helpful in this case. Possibly someone at a decisive positon invented that a list type parameter would automatically mean a table column.

    You can still invoke the function via the formula bar or the advanced editor, which will allow you to supply any list as your list argument.

    Tuesday, January 16, 2018 11:44 AM
  • Thank you for the reply, Marcel.  Unfortunately, this issue prevents less sophisticated users from adding a custom table column (via the ribbon tool) that invokes the parameter prompt for a user-defined function (saved to the catalog) which is designed to use a list of lists.
    Tuesday, January 16, 2018 4:34 PM
  • In that case I'm curious about your case and how you explain a list of lists to the less sophisticated users.

    You can still instruct them to invoke the custom function, supply some table / column, and next adjust the generated code.
    That's exactly what I did in this video. (The link starts at the relevant part of the video; the list parameter is the last parameter of the function).

    Alternatively you can consider adjusting the program logic, so you don't need a list as function parameter.

    Tuesday, January 16, 2018 5:08 PM
  • What I had to do is change my function to accept a table instead of a list of list.  The downside of this approach is that w/ each invocation from the "Add Table Column" ribbon tool the table has to be converted into a list of lists using Table.ToRows.  Here's my function's code that emulates Excel's built-in VLOOKUP function ...

    ( value as anynonnull, cases as table, column as number, optional MatchType as logical ) as any =>
    
    let
        CaseList = Table.ToRows ( cases ), 
        MatchIsTrue = if MatchType = null then true else MatchType, 
        SelectList = List.Select ( CaseList, each if MatchIsTrue then _{0} <= value else _{0} = value ),
        Result = try List.Last ( SelectList ){column-1} otherwise ""
    in
        Result

    Tuesday, January 16, 2018 6:48 PM
  • Thanks for the explanation.

    Indeed, for approximate vlookups, a buffered list would be the better option.

    Unfortunately I don't see any other alternatives then the ones already mentioned.

    Tuesday, January 16, 2018 9:19 PM
  • On second thought, still some remarks.

    It may be a nice exercise to emulate Excel's VLOOKUP function in Power Query, but I would never use it in practice.

    In case of an exact match, it is better to merge queries.
    When expanding the resulting nested tables you can easily refer to the required column by its name, rather than by the sequence number.

    You may want to avoid approximate lookups if possible. E.g. if you would have a price table with start dates, you can expand the table to have a price for each individual date, so you can merge the tables.

    Wednesday, January 17, 2018 6:18 AM
  • While this is all very academic, it doesn't help provide a custom function that can be called when using the Invoke Custom Function ribbon tool to add a custom table column.  Nor does is it address the need to pass a stand alone list rather than a table column when invoking a custom function.
    Wednesday, January 17, 2018 2:25 PM
  • I submitted an Power BI UserVoice request to have the user-defined function invocation dialog changed. 

    Please Vote for this request if you agree that such a change is needed.


    • Marked as answer by Mark Weisman Wednesday, January 17, 2018 10:35 PM
    • Edited by Mark Weisman Wednesday, January 17, 2018 10:37 PM
    Wednesday, January 17, 2018 10:35 PM
  • Hi Mark. This is a limitation in the current Invoke Function UI. Most users work with tables, not lists, so we optimized for deriving a list from a particular column of a table. But, as you point out, there are other scenarios this does not cover.

    Your best bet may be to use a single-column table for your cases, where each cell itself is a list. That way you won't have to modify your function, and can use the UI to invoke it.

    Ehren

    Thursday, January 18, 2018 1:23 AM
    Owner
  • Ehren, great idea; however, it didn't work.  Here's my code ...

    let
        Source = Table.FromRows ( {{1,"A"},{2,"B"},{3,"C"}}),
        LisOfList = Table.ToRows ( Source ),
        #"Converted to Table" = Table.FromList(LisOfList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
        #"Converted to Table"

    When I try to use this single column table with each value as a list in the Invoke Function UI I get this  ...

    Notice that there are no columns to choose from.  Evidently, the UI doesn't like columns of lists either.



    Thursday, January 18, 2018 3:24 PM
  • Ouch, you're right. Looks like we only display primitive-typed columns. Thanks for submitting this to UserVoice.

    Ehren

    Thursday, January 18, 2018 6:00 PM
    Owner
  • Thursday, January 18, 2018 7:14 PM
  • A workaround for the last issue is to disguise your column with an ascribed primitive type, e.g. text:

    let
        Source = Excel.CurrentWorkbook(){[Name="Cases"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case", Int64.Type}, {"Description", type text}}),
        Custom1 = #table(type table[Cases = text],List.Transform(Table.ToRows(#"Changed Type"), each {{_{0},_{1}}}))
    in
        Custom1
    Another point of attention: in the case of the vlookup emulation, because of performance reasons, you would want to first buffer the list of lists before invoking the function. This means that you want to refer to some other step in the same query instead of referring to another query that returns a list (of lists). I have never seen an example in which the UI lets you select another step in the current query. The merge option comes close as it lets you select the current query, but not another step in the current query. 

    Friday, January 19, 2018 4:58 AM