m code data types create list of types

[Dicken]

Hi, 

I would like to create a list of the types of data in the 'cells' not  the declared column data types , so even if 
column type = Any.Type      , but  i want for example;  Value.Type ( #date(2000,1,1)) = date  , 

i did think about using schema,  kind but that retruns a list of   'any'  see behaviour ;

let
  atable = #table(
    type table [a = Any.Type, b = Any.Type, c = Any.Type, d = Any.Type], 
    {{"cat", 100, #date(2000, 1, 1), 2.5}}
  ), 
  addcol = Table.AddColumn(atable, "N", each Table.Schema(atable)[Kind])
in
  addcol

so then i tried this; 

let
  Source =
    let
      atable = #table(
        type table [a = Any.Type, b = Any.Type, c = Any.Type, d = Any.Type],
        {{"cat", 100, #date(2000, 1, 1), 2.5}}
      ),
      addcol = Table.AddColumn(
        atable,
        "N",
        each List.Transform(Record.FieldValues(_), (x) => Value.Type(x))
      )
    in
      addcol,
  N = Source{0}[N]
in
  N

so this is close as when you drill down into each value it is the type, but i can't figure a way ( if there is one) to get 
the list  of {type,type , type , type }  to be { text, number, date , number }  , im not even sure this is possible, 
or I'm approaching from completely wrong way?

Richad. 

[ralf_anton]

Hallo,

Du kannst Dir diese Abfrage sparen, wenn Du in den Optionen die Einstellung aktivierst, dass die Spaltentypen nie erkannt werden sollen. Dann sind immer alle Spalten vom Typ any.

[grazitti_sapna]

Hi @Dicken,

You're query is correct but the use of Value.Type() returns a type object, not a readable label like text, number, date.

You should use Value.Is() instead.

I've attached a sample .pbix file with solution. 

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[Dicken]

the use of Value.Is, would not be correct, 1, value.is ,   merely says's is this an acceptabe type ie.
Value.Is( #date() , Any.Type ) = true   ,   but regadless i wanted to access the actural type, so for example;

let
    Source = #table( type table [ a = Any.Type, b = Any.Type] , 
{{"cat", 100}} ),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each 
{ Value.Is( [a], Any.Type) , Value.Is( [b], Any.Type) } )
in
    #"Added Custom"

would retrun a list  { true,, true}   ,  what i want is { text, number} . 
so thaks for the help, but it's not really a soluton. 

[grazitti_sapna]

Hi @Dicken,

If it did work for you kindly mark as accepted solution

[jennratten · Accepted solution]

Hi @Dicken - I have reviewed the solution proposed by @grazitti_sapna and think it is legitimate.  I think it just needs some explanation of how it works.

This portion of the script compares the data type of a value to a sequential list of data types and returns a mapped text value when a match is found; in this case, the mapped text value is the text representation of the data type. It does this for every field value in a record, for every record in the table.

List.Transform(
            Record.FieldValues(_),
            (x) =>
                if x = null then "null"
                else if Value.Is(x, type text) then "text"
                else if Value.Is(x, type number) then "number"
                else if Value.Is(x, type date) then "date"
                else if Value.Is(x, type datetime) then "datetime"
                else if Value.Is(x, type logical) then "logical"
                else "other"
        )

This works when it is implemented as it is designed, with a list of data types and mapped text representations. 

The Value.Is() function by itself does not return the text representation, but the solution does.

In addition, here are a few other implementations which have stood the test of time but do things slightly differently...

Transform the column types of a table to their detected data types @ImkeF 

https://gist.githubusercontent.com/ImkeF/6af3d67c91b81d9eb0adceba0261a252/raw/1ef5daebbd226e2cae1f52... 

Dynamically Detect Column Types with Power M - Microsoft Fabric Community @stevedep 

My somewhat similar take on dynamic type detection from 2021, that works for both Power Query desktop and Power Query online aka dataflows - gen1 at the time; data types do not always have parity across access points:

// ****************************************************************************************************************/
  // fnDetectDataTypesForDataFlows 
  // ****************************************************************************************************************/
  //   PURPOSE
  //   - Detect data types from a table's data and transform to the appropriate type
  // 
  //   - last revised: 2/11/2021
  // ****************************************************************************************************************/

let
  fn = (table as table, optional first_n_records as nullable number, optional culture as nullable text) as table =>
let
    TextColumns = Table.ColumnsOfType ( table, {type nullable text} ),
    TempKey = "--(^_^)--",
    ReplaceNulls = Table.ReplaceValue ( table, null, TempKey, Replacer.ReplaceValue, TextColumns),
    InvalidTypes = {type list, type record, type table, type function, type type, type null, type duration},
    Culture = if culture = null then "en-US" else culture,
    TopRows = if first_n_records = null then 200 else top_records, //set default to 200 rows to establish a column type
    TopNRows = Table.FirstN(ReplaceNulls, TopRows),
    ColumnNameList = Table.ColumnNames(TopNRows),
    ColumnDataLists = List.Accumulate(ColumnNameList, {}, (accumulated, i) => accumulated & {Table.Column(TopNRows, i)}),
    ColumnTypes = List.Transform(ColumnDataLists, (i) => List.ItemType(i)),
    TransformList = List.Select(List.Zip({ColumnNameList, ColumnTypes}), (i) => not List.Contains(List.Transform(InvalidTypes, (j) => Type.Is(i{1}, j)), true)),
    TypedTable = Table.TransformColumnTypes(ReplaceNulls, TransformList, Culture),
    List.ItemType = (list as list) =>
        let
            ItemTypes = List.Transform(
                list,
                each
                if Value.Type(Value.FromText(_, Culture)) = type number
                then
                    if Text.Contains(Text.From(_, Culture),"%") 
                    then Percentage.Type
                    else 
                        if Text.Length(Text.Remove(Text.From(_, Culture), {"0".."9"} & Text.ToList("., -+eE()/'"))) > 0
                        then Currency.Type
                        else 
                            if Int64.From(_, Culture) = Value.FromText(_, Culture) 
                            then Int64.Type
                            else type number
                else Value.Type(Value.FromText(_, Culture))
            ),
            ListItemType = Type.Union(ItemTypes)
        in
            ListItemType
in
    let
        //RemoveInvalidTypes = Table.RemoveColumns ( TypedTable, Table.ColumnsOfType ( TypedTable, {type list, type record, type table, type function} ) ),
        // dataflows currently converts all dates to datetime
        PrimitiveTypes = Table.ColumnsOfType(TypedTable, {type nullable number, type nullable text, type nullable logical, type nullable datetime}),
        NonConformingTypes = List.RemoveMatchingItems ( Table.ColumnNames ( TypedTable ), PrimitiveTypes ),
        NonConformingTypesToText = Table.TransformColumnTypes( TypedTable, List.Zip( { NonConformingTypes,
        List.Repeat( {type text}, List.Count( NonConformingTypes ) ) } ) ),
        TextColumnsNew = Table.ColumnsOfType (NonConformingTypesToText, {type nullable text} ),
        ReplaceTempKey = Table.ReplaceValue(NonConformingTypesToText ,TempKey,"",Replacer.ReplaceValue, TextColumnsNew )
    in
        ReplaceTempKey

Complete solution script from @grazitti_sapna:

let
    Source = #table(
        type table [a = Any.Type, b = Any.Type, c = Any.Type, d = Any.Type],
        {
            {"cat", 100, #date(2000, 1, 1), 2.5},
            {"dog", 200, #date(2022, 5, 10), 10},
            {"bird", null, #date(2021, 12, 25), 5.75},
            {"fish", 50, null, "oops"}
        }
    ),

    AddTypes = Table.AddColumn(
        Source,
        "DetectedTypes",
        each List.Transform(
            Record.FieldValues(_),
            (x) =>
                if x = null then "null"
                else if Value.Is(x, type text) then "text"
                else if Value.Is(x, type number) then "number"
                else if Value.Is(x, type date) then "date"
                else if Value.Is(x, type datetime) then "datetime"
                else if Value.Is(x, type logical) then "logical"
                else "other"
        )
    ),
    #"Extracted Values1" = Table.TransformColumns(AddTypes, {"DetectedTypes", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values1"

[Dicken]

I've accepted mapping solution, but it was not really what i was asking, the short answer to what i asked is 'NO', 

Power Query does NOT expose that friendly name as a field you can read.

The "text" you see in the UI is not stored as a property on the type. It’s a UI rendering, not part of the M value.

[lbendlin]

You specifically set the cloumn type to variant.  There is no way to infer the actual column type from a single row with any kind of certainty. Power Query's built-in column type inference requires at least 200 rows of data but even then you can be certain that row 201 has a value that does not match the inferred type.

What are you ultimately trying to achieve?