Types in PowerQuery cannot be compared and a Type.AsText function

Sometimes I need to retrieve the textual representation of a type in Power Query and I’m using a fairly verbose function that I’ve stolen ages ago (I believe it was here: https://ssbi-blog.de/technical-topics-english/power-query-editor-using-notepad/ ) :

let func =
(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 ,
documentation = [
Documentation.Name = " Type.AsText ",
Documentation.Description = " Returns type in text format ",
Documentation.LongDescription = " Returns type in text format ",
Documentation.Category = " Type ",
Documentation.Source = " not sure ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann: www.TheBIccountant.com. https://www.thebiccountant.com/?p=3871 . ",
Documentation.Examples = {[Description = " ",
Code = " ",
Result = " "]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))
view raw Type.AsText hosted with ❤ by GitHub

Although I don’t know how this function will be evaluated, I suspect it checks for every type from the beginning until the matching type is found. So I was tinkering with a potentially faster solution that is based on a merge of tables: Create a table with types:

Lookup Table for Types

and merge it on the type itself with the types in my original table:

Main Table with Types that need textual representation

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"

But this function will not return any matches. I also tried out a (potentially) slower version using Table.SelectColumns(Types, each [Value] = x[Types]) – but still no match. 

What I found particularly frustrating here was, that in some cases, these lookups or filters on type-columns worked. This for example:

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"

As it turns out, M is not equipped to compare types: https://social.technet.microsoft.com/Forums/en-US/6f2f0336-e57e-477f-a40e-5ffc9f0ca7be/type-equivalence?forum=powerquery 

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

This is exactly what I was experiencing: Some comparisons worked and some didn’t.

Although this is not an everyday use case, I hope this blogpost will prevent you (and me) to run into this trap (again).

Enjoy & stay queryious 😉

Comment (1) Write a comment

  1. Pingback: No Type Equivalence In M – Curated SQL

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz