CODEWITHSUNDEEP
excelpowerbipowerquerym
David542
David542

Reputation: 110083

M table using a predefined type per column

In the following M query:

let
    #"My Source" = Table.FromRecords({
        [Name="Jared Smith", Age=24],
        [Name = "Tom Brady", Age=44],
        [Name="Hello Tom", Age = null],
        [Name = "asdf", Age = "abc"]
    })
in
    #"My Source"

Why does "abc" not produce an error? Does PowerPivot use the "any" type unless the type is explicitly defined?

enter image description here

Upvotes: 0

Views: 91

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

As I mentioned in my comments: Power Query does use the ANY data type unless you specifically assign one.

in certain circumstances, such as when you get the data from an Excel Table, it will automatically detect the data type depending on whether all of the entries in the column are of the same type. Sometimes it does this incorrectly -- eg always converting Date to Date/Time, when you might really want just Date.

You can use Table.TransformColumnTypes to do this. You can even write your own routine to detect and assign data types.

You will note that if you change the DataType of your Age column to Whole Number, the abc cell will show Error

You can also write routines to handle the mixed data type in the single column by, for example, splitting them out to their own columns.

Upvotes: 1

Olly
Olly

Reputation: 7891

You can specify the type for each column using the #table function:

let
    Source = #table(
        type table
            [
                #"Name"=text,
                #"Age"=number
            ], 
            {
                {"Jared Smith", 24},
                {"Tom Brady", 4},
                {"Hello Tom", null},
                {"asdf", "abc"}
            }
        )
in
    Source

Upvotes: 1

Related Questions