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/ ) :

https://gist.github.com/ImkeF/a26b34de8c88163f10a6b0dad901670e

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:

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

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 😉