none
Are there options for the behaviour of function dialogue in M?

    Question

  • I’m wondering if there are any options for the function dialogue in M that allows me to pass the column names of the selected table (1st parameter) dynamically as allowed values in the second parameter.

    Background: When defining a function like this:

    (Table as table, ColumnName1 as text) =>

    The dialogue will present a dropdown-list of the available queries for the table to select and for the text-parameter “ColumnName1” I can create a dropdown-list by using this expression:

    Documentation.AllowedValues = Table.ColumnNames(Query1)

    I will present a dropdown-list with all column names from Query1.

    What I want to achieve is to make the query-name (“Query1”) dynamic and take the parameter that has been selected in the first dialogue as its input. Problem seems to be that the parameters will be evaluated within the following function definition and not “during the dialogue” itself.

    So my question is if there are any options that I can use to achieve this, like:

    -          Letting the 1st parameter be evaluated while clicking on the button that opens the 2nd> dialogue (dropdown-window)

    -          Split the function up into a nested function -> pass the result from the 1st function call to the second – BUT still let the function dialogue pop-up. I’ve tried this, but the problem was that as soon as one passes a parameter to a function, the dialogue will not pop up any more (the other parameters had to be defined as optional, otherwise the function call itself would return error). That’s desired behavior with standard-functions, as otherwise one would be prompted to fill in optional values, but my question is: Can one manipulate this behavior by some parameters that are yet unknown to me?)

    Thx a lot!


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com


    Thursday, June 1, 2017 5:35 AM
    Moderator

Answers

  • With a bit of cheating maybe this can be achieved.

    fn1

    let
        aFn = (y as number) => let 
                                   dgitType2 = type number meta [Documentation.AllowedValues ={1..y}],
                                   myFnType2 = type function (x as dgitType2) as record,   
                                   z = Value.ReplaceType(fn2,myFnType2)
                               in 
                                   z meta [a=y],
        dgitType = type number meta [Documentation.AllowedValues ={1..9}],
        myFnType = type function (y as dgitType) as function,
        
        out = Value.ReplaceType(aFn,myFnType)
    in
        out

    fn2

    (x)=>
    let
       y = Expression.Evaluate("Value.Metadata(#""Invoked Function"")[a]",#shared),
       out = y*x
    in
        out


    Friday, June 2, 2017 3:15 PM

All replies

  • Nice question, Imke. It will be very convenient if PQ will allow us to do that things.

    In my opinion, this question should be addressed to Power Query and Power BI developers, who set up all processes behind the scene (like defining recommended text separator or defining data types etc.).

    Even if you try to get some list of all existing table columns via Table.Schema over Section1, for example, then you need to evaluate all these tables BEFORE you'll get this list (sometimes it is hard to predict number of columns in a table before it evaluated).

    So, as we discussed in the Twitter, this option will work in UI only over pre-evaluated or fully evaluated queries cached somewhere.


    Maxim Zelensky Excel Inside

    Thursday, June 1, 2017 12:58 PM
  • An alternative is to use 1 parameter with type list.

    This will let you choose your table and column as illustrated in this video.

    A drawback is that you will only have 1 list with values without the table context (types, other columns etcetera).
    Depending on the situation this may - or may not - be a showstopper to use this approach.


    • Edited by MarcelBeug Thursday, June 1, 2017 2:38 PM
    Thursday, June 1, 2017 2:38 PM
  • Thx Marcel - this is no option for me, as I need the column names as text in order to reference them in the function.

    Although this is a nice feature, cannot think of many usecases where it makes sense. Doesn't one often want to reference these columns (with row/record-based commands) in the functions? I use Record.Field(_, ColumnName) - that's why the requirement for text.


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com

    Thursday, June 1, 2017 2:51 PM
    Moderator
  • Thx Maxim - yes, that's the problem with the current default behaviour.

    I was wondering if there are any parameters which can modify it, like letting the function dialogue pop up although only optional parameters missing (in the nested version). Or even trigger the evaluation of the parameters with the click on the next drop-down-box (then one function would be enough).


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com

    Thursday, June 1, 2017 2:57 PM
    Moderator
  • Edit June 2, 2017: this solution was originally using #sections keyword to get a record with tables for use as environment record with function Expression.Evaluate in function fnMyFunction below.
    This didn't work when loading the results, as #sections only includes tables if you are working inside the query editor; as soon as the query editor is closed and results are loaded, #sections gives different results and doesn't include the tables anymore.

    As a workaround a custom record with available tables can be created (and manually maintained...): query "MyTables" below.

    Another option would be to create a list with all "table - column" combinations from which the parameter value can be chosen. Maybe not ideal either; I'm just exploring the options.

    Query MyTables with available tables, that must be manually maintained and which looks like:

    = [Result = Result, TransactionTable = TransactionTable]

    Edit: an advantage of this workaround:  this can limit the tables to choose from when editing parameter MyTableColumn (see below).

    Query MyTableColumns to create the list (edt: amended to use MyTables instead of #sections[Section]):

    let
        Source = MyTables,
        Tabled = Record.ToTable(Source),
        Filtered = Table.SelectRows(Tabled, each [Value] is table),
        #"Removed Errors" = Table.RemoveRowsWithErrors(Filtered, {"Value"}),
        AddedColumns = Table.AddColumn(#"Removed Errors", "Columns", each Table.Schema([Value])[Name]),
        Removed = Table.RemoveColumns(AddedColumns,{"Value"}),
        Expanded = Table.ExpandListColumn(Removed, "Columns"),
        Merged = Table.CombineColumns(Expanded,{"Name", "Columns"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Table - Column"),
        TableColumns = Merged[#"Table - Column"]
    in
        TableColumns

    Parameter MyTableColumn (with example content):

    "Result - Tran_DT" meta [IsParameterQuery=true, ExpressionIdentifier=MyTableColumns, Type="Text", IsParameterQueryRequired=true]


    Function fnMyFunction (just a silly function that adds a copy of the chosen column to the chosen table):

    (MyTableColumn as text, Context as record) =>
    let
        Source = Text.Split(MyTableColumn," - "),
        MyTable = Expression.Evaluate(Source{0},Context),
        ColumnName = Source{1},
        AddedColumn = Table.AddColumn(MyTable,"Chosen column", each Record.Field(_,ColumnName))
    in
        AddedColumn


    Query that invokes the function:

    = fnMyFunction(MyTableColumn, MyTables)





    • Edited by MarcelBeug Friday, June 2, 2017 3:54 AM
    Thursday, June 1, 2017 5:52 PM
  • Hi Marcel,

    yes, that's a neat workaround! You're right to assume that I love improvisations to prove that M can do just everything :-)

    But in this special case I'm actually only interested in a neat and clean solution that works "out-of-the-box".

    Re the problem with loading: Have you tried to replace the #sections... by #shared?


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com

    Thursday, June 1, 2017 7:41 PM
    Moderator
  • Hi Imke,

    Yes, I tried anything, but it seems that query output is not available in #sections nor in #shared once you are out of the query editor.

    My guess is that this is related to a remark in the PQFL Language specification on page 7:

    "10.

    Sections provide a simple modularity mechanism. (Sections are not yet leveraged by Power Query.) "

    Cheeers,
    Marcel

    Thursday, June 1, 2017 9:49 PM
  • Hi again,

    I found a workaround for the issue with #sections: maintain a custom record with tables from which can be chosen.

    A drawback is that this must be manually maintained.
    An advantage is that you can limit this record to the tables from which actually can be chosen.

    I adjusted my original post accordingly.

    BR,
    Marcel

    Friday, June 2, 2017 3:53 AM
  • Hi Imke,

    Have a look if this would do the trick for you :)

    fn2

    (x)=>
    let
        out = x*x
    in
        out

    and the more interesting interesting

    fn1

    let
        aFn = (y as number) => let 
                                   dgitType2 = type number meta [Documentation.AllowedValues ={1..y}],
                                   myFnType2 = type function (y as dgitType2) as function,                                
                                   z = Value.ReplaceType(fn2,myFnType2)
                               in 
                                   z,
    
        dgitType = type number meta [Documentation.AllowedValues ={1..9}],
        myFnType = type function (y as dgitType) as function,
        
        out = Value.ReplaceType(aFn,myFnType)
    in
        out
    [can't submit the gif yet]
    Friday, June 2, 2017 10:24 AM
  • Thank you Igor - that looks awesome!!

    However, having difficulties to understand the code. Could you please adjust it so that it returns a record with 2 fields: Field1 containing the result of the first call and Field2 the result of the 2nd?

    Million thx for that!!!


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com

    Friday, June 2, 2017 11:22 AM
    Moderator
  • Friday, June 2, 2017 12:03 PM
  • With a bit of cheating maybe this can be achieved.

    fn1

    let
        aFn = (y as number) => let 
                                   dgitType2 = type number meta [Documentation.AllowedValues ={1..y}],
                                   myFnType2 = type function (x as dgitType2) as record,   
                                   z = Value.ReplaceType(fn2,myFnType2)
                               in 
                                   z meta [a=y],
        dgitType = type number meta [Documentation.AllowedValues ={1..9}],
        myFnType = type function (y as dgitType) as function,
        
        out = Value.ReplaceType(aFn,myFnType)
    in
        out

    fn2

    (x)=>
    let
       y = Expression.Evaluate("Value.Metadata(#""Invoked Function"")[a]",#shared),
       out = y*x
    in
        out


    Friday, June 2, 2017 3:15 PM
  • Very smart!!

    Trying to adjust it to my usecase where the current queries shall pop up in the dropdown-selection of the first call ... might need to call you in on that again, but will give it a try myself first ;-)

    Thank you very much!!


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com

    Friday, June 2, 2017 6:00 PM
    Moderator
  • Sorry for coming back again so late, lost it out of sight a bit.

    Although very smart, this solution doesn't really work for me:

    1) The name of the 1st function call is hardcoded in the Expression.Evaluate -command. So if I have more than one function call in a query, it will reference the wrong query.

    2) When trying to combine it into one query, an error was thrown that [a] couldn't be found.

    3) Also I didn't find a way how to make the current queries appear in the dropdown-list

    The only reason why I have asked for this functionality is to create a non-intimidating query experience for newbies.

    With that said, I'd prefer the results of the 1st function call (the one returning the allowed values) not to appear on the query pane but to stay hidden instead, as it is only providing input for the following query.

    I’ve posted an idea here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/19611499-improve-function-dialogue-auto-generate-list-of-a

    But will keep this thread open a bit in the hope that there is actually a way to do it today that we’re not aware of yet ;-)

    Thanks again!!


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com

    Friday, June 16, 2017 5:00 AM
    Moderator
  • Closing this thread with the solution that came closest :-)

    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, July 2, 2017 6:37 AM
    Moderator
  • Some additional info to this thread, although still a workaround.

    I have a function that takes a table as input plus 3 of its column names as additional parameters; a nice example in which it is quite convenient to choose column names from a list. The workaround provides an easy way to generate the list of column names (which can then be used in Documentation.AllowedValues as outlined by Imke in the original question).

    I created a custom function TableColumnNames with code: =Table.ColumnNames

    I created a query InputTableColumnNames with code:

    let
        Source = #table(0,{{}}),
        AddedTableColumns = Table.AddColumn(Source, "TableColumns", each TableColumnNames(EventsPrep)),
        TableColumns = AddedTableColumns{0}[TableColumns]
    in
        TableColumns

    The added value of this workaround is that you can choose your table from a list, if you use the gear button right from step AddedTableColumns:

    So all that's left is some "clickedeeclack" as illustrated in this video.

    Thursday, October 12, 2017 10:17 AM
  • Hi Marcel,

    that looks very interesting! How did you create the custom types (like "DateTimeColumn")?


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, October 12, 2017 10:36 AM
    Moderator
  • I created Info records and added these as meta data to the parameters.

    The relevant part of my code:

        P1Info =
            [Documentation.Name = "Table",
             Documentation.FieldCaption = "Choose input table:"],
    
        P2Info =
            [Documentation.Name = "DateTimeColumn",
             Documentation.FieldCaption = "Column with date/times to be converted:",
             Documentation.AllowedValues = InputTableColumnNames],
    
        P3Info =
            [Documentation.Name = "ZoneFromColumn",
             Documentation.FieldCaption = "Column with originating time zones:",
             Documentation.AllowedValues = InputTableColumnNames],
    
        P4Info =
            [Documentation.Name = "ZoneToColumn",
             Documentation.FieldCaption = "Column with destination time zones:",
             Documentation.AllowedValues = InputTableColumnNames],
    
        P5Info =
            [Documentation.Name = "NewColumnName",
             Documentation.FieldCaption = "New column with converted date/times:"],
    
        FunctionType = 
            Type.ForFunction(
                [ReturnType = type table, 
                 Parameters = 
                    [Table = type table meta P1Info,
                     DateTimeColumn = type text meta P2Info,
                     ZoneFromColumn = type text meta P3Info,
                     ZoneToColumn = type text meta P4Info,
                     NewColumnName = type text meta P5Info]],
                5) meta Documentation,

    Edit: also relevant is that the FunctionType is applied later to the core function (TACDT):

        TableAddConvertedDateTime = Value.ReplaceType(TACDT,FunctionType)
    

    The function definition of TACDT (without function body):

        TACDT = (Table as table, DateTimeColumn as text, ZoneFromColumn as text, ZoneToColumn as text, NewColumnName as text) as table =>
    


    • Edited by MarcelBeug Thursday, October 12, 2017 10:56 AM
    Thursday, October 12, 2017 10:51 AM