Power Query - Documentation.AllowedValues. Extra item with optional function param

Lz._ 9,016 Reputation points
2021-05-12T09:34:50.15+00:00

Hi

The main idea:

let  
    fnTest = (#"Week start" as text) as text =>  
        "Selected item: " & #"Week start",  
//  
    fnType = type function  
    (  
        #"Week start" as (type text meta  
            [ Documentation.AllowedValues = {"Sun","Mon"} ]  
        )  
    ) as list meta []  
in  
    Value.ReplaceType(fnTest, fnType)  

However, in the customer's scenario the function's parameter should be optional:

let  
    fnTest = (optional #"Week start" as nullable text) as text =>  
        Text.Combine({"Selected item: ",  
            if #"Week start" is null then "Mon" else "Sun"}  
        ),  
//  
    fnType = type function  
    (  
        optional #"Week start" as (type nullable text meta  
            [ Documentation.AllowedValues = {"Sun","Mon"} ]  
        )  
    ) as list meta []  
in  
    Value.ReplaceType(fnTest, fnType)  

but in this case there's an extra blank/empty item at the top of the list :(

95955-optionalparam-allowedvalues-extraitem.png

The Basic Example on Adding function documentation shows the same behavior for the optional Count param. Searched the Net and found this pretty good article where the 4th picture shows the same thing

Does someomone see an option to expose a clean list to the users? Thanks

EDIT

let  
    fnTest = (optional #"Week start" as nullable text)=>  
        #"Week start" is null,  // TRUE when the add. "blank" item is selected  
  
    fnType = type function(  
        optional #"Week start" as (type nullable text meta  
            [ Documentation.AllowedValues = {"Sun","Mon"} ]  
        )  
    ) as list meta []  
in  
    Value.ReplaceType(fnTest, fnType)  

Tried many things to get rid of that null item:

AllowedValues=List.RemoveNulls({"Sun","Mon"})  
AllowedValues=List.Skip({"Sun","Mon"})  
AllowedValues=List.LastN({"Sun","Mon"}, 2)  
AllowedValues=List.Buffer({"Sun","Mon"})  
AllowedValues=Table.FromColumns({{"Sun","Mon"}},type table[foo=text])[foo]  
AllowedValues=List.Buffer(Table.FromColumns({{"Sun","Mon"}},type table[foo=text])[foo])  

...to no avail. so far :(

Community Center Not monitored
{count} votes

Accepted answer
  1. Lz._ 9,016 Reputation points
    2021-05-18T08:39:25.957+00:00

    The extra null/blank at the top of an optional list of AllowedValues is a product behavior - see above comments

    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.