none
Value.Type - How to get/access the corresponding text value/string? RRS feed

  • Question

  • Hi

    I've been trying/searching alternatives to this for a moment to no avail.

    [Value] are of mixed type. In another column I need to get  (illustrated in [Expected_Value]) the actual text value/string "sitting behind" what's returned in [Value.Type] (...Value.Type([Value]) - Understand this returns a Type type)

    So far I've been using the following to get the "Expected_Value" but it seems overcomplicated to me. I'm quite sure there's something easier/more straightforward:

    step = Table.AddColumn(PreviousStep, "Expected_Value",
            each Table.Schema(Table.FromValue([Value]))[Kind]{0}, type text)

    Thanks


    • Edited by Lz._ Thursday, February 7, 2019 12:07 PM Picture did not load
    Tuesday, February 5, 2019 3:55 PM

Answers

  • Believe it or not, you've actually found the simplest solution. :)

    I use a similar technique in my Type_ToText  function - one of the functions in my custom Type functions library. The main addition to what you've done is that the function also converts a nullable types to text equivalents:

    (typ as type) as text => 
    let
        Table = #table(type table [Column1 = typ], {{null}}),
        TypeDescription = Table.Schema(Table)[Kind]{0},
        TypeString = if not List.Contains({"any", "null", "none"}, TypeDescription) then
                         if Type.IsNullable(typ) then "nullable " & TypeDescription else TypeDescription
                     else TypeDescription
    in
        TypeString

    • Marked as answer by Lz._ Thursday, February 7, 2019 10:12 AM
    Tuesday, February 5, 2019 7:22 PM
  • Hi Lz_,

    Difficult to believe…

    See an insider's solution at this link (TextType function):  https://gist.github.com/CurtHagenlocher/dceef4a19a3124cb172244bca573cecc

    which is a much longer code-wise. Curt didn't use Table.Schema (probably an oversight, or he knows something we don't), but the point is if there was something more obvious (like a built-in function that would get the job done without using Table.Schema), he'd know about it. 

    I'm sure that a similar solution to the one in the link was posted on this forum, but I can't locate it.

    However I don't see in which context/scenario it would return "nullable blablabla".

    Notice that Curt's function tests all type inputs for the possibility of a nullable entry. The custom functions are for general purpose use, so if someone enters type nullable date into the function, you want to return "nullable date" and not just "date."

    From the standpoint of getting nullable values from table columns, I'm not entirely sure. All columns in Excel or text files are nullable by default, so in a changed type step, setting a column type to say, type nullable date, is the same as type date, and any test for nullability fails. I don't know if the same logic applies to database files (which provide type metadata for PQ to use). That's something I'll have to test.

    If both options (yours & mine) work, how do we close that case? I can mark yours as answer but I don't think it is possible to mark mine as it's the initial question.

    It depends on what you consider to be an answer. Confirmation that your initial instincts were correct, or that what you posted was the best solution? I really don't mind either way. I don't think that anyone would care if you marked your original question as answered, as the context of the thread provides the justification for doing so. You can add an update statement to your original post saying something like "Based on the discussions in this thread since my original post ...…."

    • Marked as answer by Lz._ Thursday, February 7, 2019 10:13 AM
    Wednesday, February 6, 2019 8:42 PM

All replies

  • Hi Lz_,

    So you're looking for something like a type to the text representation of a type function (e.g. type text to "text")?


    Tuesday, February 5, 2019 5:27 PM
  • Hi Colin

    So you're looking for something like a type to the text representation of a type function (e.g. type text to "text")?

    If question well understood, answer would be yes. In below picture [Value.Type] has function
    =...Value.Type([Value]) and as expected returns a Type type. If I click i.e. in that column #row 4I get (on the screen) => 'datetime'

    and what I can't figure out is if there is a way to get that 'datetime' value in a var., a column…
    Last column just illustrate what I'd like to get. Hope this clarifies

    Tuesday, February 5, 2019 6:25 PM
  • Believe it or not, you've actually found the simplest solution. :)

    I use a similar technique in my Type_ToText  function - one of the functions in my custom Type functions library. The main addition to what you've done is that the function also converts a nullable types to text equivalents:

    (typ as type) as text => 
    let
        Table = #table(type table [Column1 = typ], {{null}}),
        TypeDescription = Table.Schema(Table)[Kind]{0},
        TypeString = if not List.Contains({"any", "null", "none"}, TypeDescription) then
                         if Type.IsNullable(typ) then "nullable " & TypeDescription else TypeDescription
                     else TypeDescription
    in
        TypeString

    • Marked as answer by Lz._ Thursday, February 7, 2019 10:12 AM
    Tuesday, February 5, 2019 7:22 PM
  • Hi Colin

    Believe it or not, you've actually found the simplest solution
    Difficult to believe…

    No problem to understand what your function does. However I don't see in which context/scenario it would return "nullable blablabla". If you have a couple of examples in mind I will appreciate

    If both options (yours & mine) work, how do we close that case? I can mark yours as answer but I don't think it is possible to mark mine as it's the initial question. Suggestion => update your last reply to include my option and I'll mark that reply as answer - makes sense? 

    Wednesday, February 6, 2019 1:15 PM
  • Hi Lz_,

    Difficult to believe…

    See an insider's solution at this link (TextType function):  https://gist.github.com/CurtHagenlocher/dceef4a19a3124cb172244bca573cecc

    which is a much longer code-wise. Curt didn't use Table.Schema (probably an oversight, or he knows something we don't), but the point is if there was something more obvious (like a built-in function that would get the job done without using Table.Schema), he'd know about it. 

    I'm sure that a similar solution to the one in the link was posted on this forum, but I can't locate it.

    However I don't see in which context/scenario it would return "nullable blablabla".

    Notice that Curt's function tests all type inputs for the possibility of a nullable entry. The custom functions are for general purpose use, so if someone enters type nullable date into the function, you want to return "nullable date" and not just "date."

    From the standpoint of getting nullable values from table columns, I'm not entirely sure. All columns in Excel or text files are nullable by default, so in a changed type step, setting a column type to say, type nullable date, is the same as type date, and any test for nullability fails. I don't know if the same logic applies to database files (which provide type metadata for PQ to use). That's something I'll have to test.

    If both options (yours & mine) work, how do we close that case? I can mark yours as answer but I don't think it is possible to mark mine as it's the initial question.

    It depends on what you consider to be an answer. Confirmation that your initial instincts were correct, or that what you posted was the best solution? I really don't mind either way. I don't think that anyone would care if you marked your original question as answered, as the context of the thread provides the justification for doing so. You can add an update statement to your original post saying something like "Based on the discussions in this thread since my original post ...…."

    • Marked as answer by Lz._ Thursday, February 7, 2019 10:13 AM
    Wednesday, February 6, 2019 8:42 PM
  • Hi Colin

    Difficult to believe…
    Don't get me wrong, I wasn't saying I didn't trust you re. "Believe it or not, you've actually found the simplest solution". I was just surprised it could be so complex to get such info. Looks like I'm too much influenced by desktop apps… Good learning

    If Curt (we know where he works/used to work) developped such function, sure there's no straightforward way to get the info. I could recognized part of your function in Curt's code and yours is probably enough in most case but good to know it exists

    if someone enters type nullable date into the function, you want to return "nullable date" and not just "date."
    I see what you mean and that make sense then

    From the standpoint of getting nullable values from table columns, I'm not entirely sure. All columns in Excel or text files are nullable by default, so in a changed type step, setting a column type to say, type nullable date, is the same as type date, and any test for nullability fails. I don't know if the same logic applies to database files (which provide type metadata for PQ to use). That's something I'll have to test
    When I asked for a couple of examples I had databases in mind. If you investigate this I'm interested by your findings but don't do this for me please - no need, it's more curiosity for better understanding

    It depends on what you consider to be an answer
    For me everything that works (robust) is an option for one to test in real conditions before implementation. And we know that the best solution - assuming it exists - is not always the one an OP/Customer will choose…
    Let's not waste time on this. It's probably not a topic for a lot of people and the few who will be will probably take the time to read the thread end to end => Will mark your function + previous reply with the link to Curt's function

    Thanks much again

    Thursday, February 7, 2019 10:12 AM
  • I could recognized part of your function in Curt's code and yours is probably enough in most case but good to know it exists

    That's because I started with Curt's code, but didn't really like all of the 'ifs', and repetition of the Type.IsNonNullable function. I had the investigate if there was a better way. I thought using Table.Schema was an elegant solution, until you came along thought it was too complicated! :-)

    I left out the check for 'any' in Curt's code, probably because at the time I thought it to be unlikely (basically, if you enter the argument 'type anynonnull', it will return 'any', unless you use this check). I've now decided to add this check to my function for completeness sake.

    When I asked for a couple of examples I had databases in mind. If you investigate this I'm interested by your findings but don't do this for me please - no need, it's more curiosity for better understanding.

    Actually, there's a problem using the custom functions with databases. Databases use specific numeric types that get lost when doing conversions to text. Type facets were probably introduced into Power Query to address this issue (a facet is a subset of a primitive type - e.g. Int64.Type is a facet of type number). The 'Kind' column in Table.Schema displays the primitive types, but the 'TypeName' column provides the facets like Int64.Type, Int32.Type, Currency.Type, and so on). Another column, 'IsNullable', specifies whether the type is nullable or not.

    Another thing is that if you want the types from a table in general (database or not), the types are across the columns and not within the column (like your example).

    So for any table, I'd use the following approach:

    Schema = Table.Schema(PreviousStep),
    Types = Table.SelectColumns(Schema, {"Name", "TypeName", "IsNullable"})
    
    By the way, one good use of Type_ToText is with =#shared. The Value column (which results after converting to a table), has many different types in the column. If you want to filter just the functions, you can add a Value.Type column and a Value.TextType column. You can then filter Value.TextType column to display only the list of functions.


    Thursday, February 7, 2019 5:00 PM
  • So for any table, I'd use the following approach:

    Schema = Table.Schema(PreviousStep),
    Types = Table.SelectColumns(Schema, {"Name", "TypeName", "IsNullable"})

    Makes totally sense!!

    By the way, one good use of Type_ToText is with =#shared. The Value column (which results after converting to a table), has many different types in the column. If you want to filter just the functions, you can add a Value.Type column and a Value.TextType column. You can then filter Value.TextType column to display only the list of functions
    I'm at lost for now. Will try to figure it out… (still have a hard time to play with #shared)

    Thanks again


    • Edited by Lz._ Thursday, February 7, 2019 6:43 PM
    Thursday, February 7, 2019 6:43 PM
  • Hi Colin

    By the way, one good use of Type_ToText is with =#shared. The Value column (which results after converting to a table),...

    I was initially confused by "The Value column" as that column name is also used to illustrate my intitial request. After a good night everything is 100% clear:

    let
        Source = #shared,
        SourceAsTable = Record.ToTable(Source),
        AddedValueType = Table.AddColumn(SourceAsTable, "Value.Type", each
            Value.Type([Value]), type type),
        AddedValueTextType = Table.AddColumn(AddedValueType, "Value.TextType",
            each Type_ToText([Value.Type]), type text)
    in
        AddedValueTextType
    
    // And THANK YOU Colin

    You can then filter Value.TextType column to display only the list of functions
    This is exactly what I ultimately wanted to acheive when I raised that case > filter #shared

    Friday, February 8, 2019 1:14 PM