none
Type equivalence

    Question

  • The PQ Formula Language Specification (chapter 5.9) states:

    "Type equivalence is not defined in M. Any two type values that are compared for equality may or may not return true."

    Unfortunately, I have no idea what is meant.

    type text = type text   //true

    type text = type number  //false

    Value.Equals(type text, type text)  //true

    Value.Equals(type text, type number) //false

    and so on ...

    I can neither produce a type comparison with the "=" operator nor with the Value.Equals function where the result is wrong. 

    Does anybody know what is meant?

    Thursday, February 5, 2015 1:19 PM

Answers

All replies

  • M> type {number} = type {number}
    false
    • Proposed as answer by Curt Hagenlocher Thursday, February 5, 2015 5:12 PM
    • Marked as answer by frank-t Sunday, February 8, 2015 1:45 PM
    Thursday, February 5, 2015 5:12 PM
  • Just spent some unfruitful hours on this as well: This will mean that you cannot use types in a lookup/merge operation or select a table of types with a comparison to a type defined somewhere else.

    So this will NOT work:

    let
        Source = #shared,
        Functions = Record.ToTable(Source),
        #"Added Custom" = Table.AddColumn(Functions, "Type", each Value.Type([Value])),
        #"Kept Last Rows" = Table.LastN(#"Added Custom", 1),
        Types = Record.ToTable([table = type table, function = type function]),
        #"Merged Queries" = Table.NestedJoin(#"Kept Last Rows",{"Type"},Types,{"Value"},"Types",JoinKind.LeftOuter),
        #"Expanded Types" = Table.ExpandTableColumn(#"Merged Queries", "Types", {"Name"}, {"Name.1"})
    in
        #"Expanded Types"


    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, February 7, 2019 9:15 PM
    Moderator
  • Hi Imke,

    I've never seen a join that wasn't based on a standard scalar value (number, text, date, etc.), so I wouldn't have expected your code to work.

    Can't think of a real-world application for such a lookup, but if you have two tables each with a type column, and you want to join the tables based on types, you should add a new column to each table that converts the types to text equivalents, and join on these text columns.

    Friday, February 8, 2019 6:15 AM
  • Hi Colin,

    converting to text equivalents was what I was trying to avoid.

    A merge on type works here:

    let
        Source = #shared,
        Functions = Record.ToTable(Source),
        #"Added Custom" = Table.AddColumn(Functions, "Type", each Value.Type([Value])),
        #"Kept Last Rows" = Table.LastN(#"Added Custom", 1),
        Types = Record.ToTable([table = type table, function = type function]),
        #"Merged Queries" = Table.NestedJoin(Types,{"Value"},Types,{"Value"},"Types",JoinKind.LeftOuter),
        #"Expanded Types" = Table.ExpandTableColumn(#"Merged Queries", "Types", {"Name"}, {"Name.1"})
    in
        #"Expanded Types"

    …. but not on my first case. So I thought that was a good example for the topic of this post.

    BTW: Same results with Table.SelectColumns on the types column.


    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!


    Friday, February 8, 2019 9:40 PM
    Moderator
  • Hi Colin,

    wondering if you have a (potentially) faster function to retrieve the textual representation of types than this one:

    (t as type) as text =>
        let
            nonNullableType = Type.NonNullable(t),
            TypeDescription = if Type.Is(nonNullableType, type binary) then "binary"
            else if Type.Is(nonNullableType, type date) then "date"
            else if Type.Is(nonNullableType, type datetime) then "datetime"
            else if Type.Is(nonNullableType, type datetimezone) then "datetimezone"
            else if Type.Is(nonNullableType, type duration) then "duration"
            else if Type.Is(nonNullableType, type function) then "function"
            else if Type.Is(nonNullableType, type list) then "list"
            else if Type.Is(nonNullableType, type logical) then "logical"
            else if Type.Is(nonNullableType, type none) then "none"
            else if Type.Is(nonNullableType, type null) then "null"
            else if Type.Is(nonNullableType, type number) then "number"
            else if Type.Is(nonNullableType, type record) then "record"
            else if Type.Is(nonNullableType, type table) then "table"
            else if Type.Is(nonNullableType, type text) then "text"
            else if Type.Is(nonNullableType, type time) then "time"
            else if Type.Is(nonNullableType, type type) then "type"
            else if Type.Is(nonNullableType, type action) then "action"
            else if Type.Is(type anynonnull, nonNullableType) then "any"
            else error "Unknown type",
            TypeString = if TypeDescription = "any" then
                             if Type.IsNullable(t) then
                                 "any" else "anynonnull"
                             else if Type.IsNullable(t) then
                                 "nullable " & TypeDescription
                             else TypeDescription
        in
            TypeString


    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!

    Friday, February 8, 2019 9:43 PM
    Moderator
  • Hi Imke,

    Can't say that it's faster, but the code in the Type_ToText function at the following link is lot more compact.

    https://social.technet.microsoft.com/Forums/en-US/f764c990-a9ce-44a0-9961-307702ec3282/valuetype-how-to-getaccess-the-corresponding-text-valuestring?forum=powerquery

    Relooking at your merged queries, it dawned on me why it doesn't work with your two different tables. 

    The type that you get from Value.Type[Value] is not the same as 'type function'. Although Value.Type might display 'function', function here is a complex type, i.e. there is more data than Value.Type displays. The same applies for tables, records, and lists.

    To get to the additional data contained in the evaluation of Value.Type, you have to use an appropriate type function. The data contained for a function are the function's parameters, the function's required parameters, and the function's return type.

    As an example, if you add another column to your #"Added Custom" table using the formula ...each Function.Parameters([Type]), a record is returned containing the parameter names for the functions in table (and error will be returned if the type in the column is not a function). 

    So Value.Type([Value]) <> type function and the lookup fails. Like the self-join you did with the Type table, self-joining "#Kept Last Rows" also works.


    Saturday, February 9, 2019 12:47 AM
  • Thanks a lot Colin,

    that's exactly what I was looking for! 


    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!

    Saturday, February 9, 2019 7:50 AM
    Moderator