Power BI Data Type Mappings

Recently we had the task of dynamically creating a Dataflow definition (JSON) based on a SQL Server Database schema (whilst inferring the "correct" corresponding type etc).

This meant getting the column type (using the Microsoft.SqlServer.Management.Smo namespace in .NET), and finding the corresponding type for both the "entity attribute type" in the Dataflow JSON defintion, and the M/Power Query type for the document query within the Dataflow definition.

Figuring out the corresponding types proved more painful than initially expected. More generally, we found that types are subtly different across all mediums within Power BI, so the report below intends to clarify what the corresponding types are between those various mediums.

For clarification, here are my definitions of the "different" mediums:

M (Power Query) types:

The data types as defined in the M language. You can find the M script for your queries in the "Advanced Editor" in both the Dataflow and Desktop Query Editor. There are more data types that you can utilize in the M language (type list for example), but I'm trying to choose only the data types for which there are "equivalents" in as many of the highlighted mediums as possible.  If you want to dive into more detail, Miguel Escobar has written a great blog post specifically about Power Query types and their conversions in Power BI.

Dataflow Query Editor types:

The visual types you see in the Dataflow Query Editor. These can be accessed by selecting the icon to the left of the column name, or in the "Data type" dropdown under the "Transform" tab.

Dataflow Query JSON export types:

The types you define for the "entity attributes" in a Dataflow JSON definition. These are equivalent to the types you see on the "Entities" page you land on when you select an existing Dataflow in the Power BI Service.

Desktop Query Editor types:

The visual types you see in the Desktop Query Editor. Like the Dataflow Query Editor, these can be accessed by selecting the icon to the left of the column name, or in the "Data type" dropdown under the "Transform" tab.

Desktop data view:

The types you see in the Desktop Data View. How the data types are accessed depends on whether you've enabled the new (currently preview) ribbon:

• If you are using the new ribbon, select a column, then navigate to "Column tools" in the ribbon and look for the "Data type" field.
• If you aren't using the new ribbon, select a column, then navigate to "Modeling" in the ribbon and look for the "Data type" field.

DAX types:

The types used in DAX. I've decided to represent them as the arguments you pass into the DataType parameter for the DATATABLE DAX function. There are some synonyms for these types, though. The SQL BI team has provided a handy summary of all of DAX's types here: DAX Data types. If you've followed that link, you'll notice two types missing from the table below: Variant and Binary. I've chosen to exclude these types, since Variant doesn't really have equivalents across the board, and Binary "isn't available in DAX" (as per the DAX Guide documentation).

T-SQL types:

T-SQL data types, as used in SQL Server. Again, there are more types than listed in the report below, but the ones listed below are the primary data types. A full list can be viewed here.

Notes and observations

• The report is best viewed full-screen!
• You might have noticed that there isn't a one-to-one mapping of types across the board. For example, the M Percentage.Type and type number both get parsed into a double for the Dataflow JSON defintion.
• This report isn't trying to show all the possible conversions from Type A to Type B - rather (as already mentioned), it's trying to give an idea of the "corresponding" or "best fit" data type across these mediums.
• These data types aren't necessarily stored in the same way across the different mediums.
• There are a couple of types that aren't supported on Dataflow/Dataset load (identified by an asterisk). If you try to apply your M queries with a column of the type binary, for example, you'll hit an error.
• Connecting to a Dataflow from the Power BI Desktop Query Editor automatically converts Currency and Percentage columns to Decimal number (weird?)
• The Duration type can't be applied upon Dataflow load (it just won't let you), but it can be applied on dataset load. However, on dataset load, it's converted to a Decimal number with Format: Whole number , where the value has been rounded to the nearest "day". So 1.11:59:59 will convert to 1, and 1.12:00:00 will convert to 2.
• Any column named "Percentage" makes the "Name" field under "Column tools" show the value #,0.##%, which seems to be a custom format string. Weird? I think it's a bug.

This blog was written in February 2020, so you may be reading this at a time when subjects of this blog may have evolved.

I'll try to keep on top of this when I notice any changes. Otherwise, if you have any feedback - don't hesitate to comment below!