Text from values and types

Power Query
1

Here is the sample PBIX file:

I stumbled across the #shared object yesterday. I want to categorise the shared objects by type i.e table, function, number etc. The only way I found to do this was an ugly long conditional custom column. Here is my transform:

let
Source = #shared,
to_table = Record.ToTable(Source),
sort_asc = Table.Sort(to_table,{{"Name", Order.Ascending}}),
// try checking Function or else
check_type = Table.AddColumn(
    sort_asc, "type_text", 
    // Values are not recognised as types
    each if Value.Is([Value], type function) then "function"
    else if Value.Is([Value], type table) then "table"
    else if Value.Is([Value], type list) then "list"
    else if Value.Is([Value], type record) then "record"
    else if Value.Is([Value], type number) then "number"
    else if Value.Is([Value], type text) then "text"
    else if Value.Is([Value], type time) then "time"

    // Types are not recognised as Values
    else if Type.Is([Value], type binary) then "binary"
    else if Type.Is([Value], type table) then "table"
    else if Type.Is([Value], type list) then "list"
    else if Type.Is([Value], type record) then "record"
    else if Type.Is([Value], type number) then "number"
    else if Type.Is([Value], type text) then "text"
    else if Type.Is([Value], type time) then "time"
    else if Type.Is([Value], type datetime) then "datetime"
    else if Type.Is([Value], type datetimezone) then "datetimezone"
    else if Type.Is([Value], type date) then "date"
    else if Type.Is([Value], type duration) then "duration"
    else if Type.Is([Value], type logical) then "logical"
    else if Type.Is([Value], type type) then "type"
    else if Type.Is([Value], type null) then "null"
    else if Type.Is([Value], type none) then "none"
    else if Type.Is([Value], type any) then "any" // condition true for everything so put last

    else "other") // should be no other types: all should be categorised
in
check_type

Question 1

Is there a cleaner way to inspect types and categorise the #shared object rows by type? I couldn’t sort by [Values] column without creating a new column.

Question 2

Why is function a Value and not a Type? When I tried to inspect it using Type, the error was something like: “Cannot convert Function to Type”.

2

Hi @izzleee,

Q1. Alternatively you could explore this Blog article by Erik Svensen, link can be found here.

Q2. Function values create and invoke other M functions, they take a type and return a type.
You will find more details about Types and the Type system in the online documentation.

I hope this is helpful.

1 Like