m code data types create list of types

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/1ef5daebbd226e2cae1f52f9b28a6dd814225001/TransformColumnTypesDynamically.pq 

 

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"