Column data types from table

Power Query
1

In M Language code, is there a way to get the column types that have been given to the column as a whole?

Below is what I am currently getting, but these are not what I am looking for.

ColumnType = Value.Type(Table.Column(myTable, myTableColumn))
- returns a list datatype, so each column is a list, this is not what I am seeking

ColumnType = Value.Type(Table.Column(myTable, myTableColumn){0})
- returns only the datatype of the first row of the column

I would like to get a list of the Type that has been assigned to the entire column for each column in a table. How to do that?

Get Column Data Type.pbix (28.4 KB)
Book1.xlsx (21.7 KB)

2

Hi @powerbideveloper,

Use Table.Schema it returns a table describing the columns of the table provided.

Table.Schema - PowerQuery M | Microsoft Learn

1 Like
3

Hello!

I’m not 100% clear on what you are asking. You will need to share
more details about your data model, table structure and visualization
needs if you want some help doing this.

The quick answer would be “You need to re-structure your data. In a
proper PowerBI data model, there is no such thing as different data
types in the same column.”

But a few points:

The Storage engine (Vertipaq engine) used by SQL SQL Server Analysis Services
Tabular, Power BI, and Power Pivot, is a columnar database capable of incredible
performances, both in speed and compression. The database requires all data in a
column to the be of the same data type.

Columnar databases stores and compresses data in a different way to
traditional row-store databases.

Columnar databases are optimized for vertical data scanning, which means that
every column is structured in its own way and physically separated from other columns

=–==-=

If you would like to examine the data models schema for more information, add the following query to the same Power BI file you submitted to the forum:

let
    Source = Sheet1,
    // get the schema of the table as a table
    Schema = Table.Schema ( Source ),  
   //select only the name and type columns from the schema table
    Summary = Table.SelectColumns(Schema,{"Name", "TypeName"})   

in
    Summary
4

@Melissa,

Thanks, Melissa! I now remember when I gave this as an answer to one of my own questions a while back. It was about finding the position of column headers in a table. I’ve just replaced “Position” with “TypeName” and it works great!