Power Query - M formula issue - Value.Type

[highxsky]

Hello all,

I come to you with an issue that I encounter on Power Query.

Basically, I have a data set with a column made of all types of data : numbers, texts, dates...

I created an M command as a new row, to show the data type for each cell of that column :
--> Value.Type([Column])

So far, it works relatively well : it creates a column with data displayed in yellow, stating "Type" and when I click on it, it shows the data type.
--> Yet, when I go to Power BI Viz and fetch that column (e.g. in a Table), it shows [Type] and not the actual type inside.

What I want to do is basically create an additionnal column in my data set and have the data type specified next to the value.
- e.g. if it's a date, it is written DATE, etc...

I am new to M and tried several things but it did not do anything good. Furthermore, while checking on the web for other related threads, I haven't found what I'm looking for.
--> I assume this is pretty simple though, as the column is created and the type inside is reckognized.

I have added two photos to illustrate my issue :
- First photo is the command written in M
- Second photo is the visual result in Power Query

Many thanks for your help and explainations in particular !

Regards,

Command in MVisual Result

[Nolock · Accepted solution]

Hi @highxsky,

I also don't know any function which can convert a type to text. But I'd like to offer a workaround.
There are only few data types in PowerQuery and you can write a function with some if/else's for each of them. The function checks if the data type is i.e. Text.Type and returns "text" and so on for other data types.

fnTypeToText = (valueType as type) as text =>
        if Type.Is(valueType, Text.Type) then
            "text"
        else if Type.Is(valueType, Number.Type) then
            "number"
        else
            "unknown",

    
    TypeOfValue = Table.AddColumn(Source, "Type of value", each fnTypeToText(Value.Type([Column1])), type text)