Option for checking all partitions against changed data types

[zenzeinet]

The Update Table Schema is giving me some wrong suggestions on updating the data types of imported data, in particular to change a double to a int.
The data loaded to the table comes from different PowerQuery (M) partitions. The issue might be related (I need to check) to the different M partitions providing different data types. But it could be an idea to check all the partitions to see if they return different data types for the same column.

[otykier]

Thanks for reporting.

Currently, TE3 uses the following mapping from Power Query data types to Tabular data types:

PQ TypeName	Tabular Data Type
binary.type	Binary
binary	Binary
varbinary	Binary
variant	Binary
sqlvariant	Binary
any.type	String
text.type	String
text	String
string	String
char	String
nchar	String
varchar	String
nvarchar	String
number.type	Double
double.type	Double
single.type	Double
percentage.type	Double
duration.type	Double
decimal.type	Double
number	Double
real	Double
float	Double
single	Double
double	Double
currency.type	Decimal
currency	Decimal
decimal	Decimal
numeric	Decimal
money	Decimal
smallmoney	Decimal
int64.type	Int64
int32.type	Int64
int16.type	Int64
byte.type	Int64
int	Int64
integer	Int64
whole	Int64
byte	Int64
bigint	Int64
smallint	Int64
tinyint	Int64
int64	Int64
int32	Int64
int16	Int64
long	Int64
datetimezone.type	DateTime
datetime.type	DateTime
date.type	DateTime
time.type	DateTime
datetime	DateTime
date	DateTime
time	DateTime
logical.type	Boolean
boolean	Boolean
bool	Boolean
bit	Boolean

There is a known bug with decimal.type mapping to Double, where it should actually map to Decimal, but this seems unrelated to your issue. Perhaps one of the PQ types that maps to Int64 should be changed.

May I ask you to check what the Power Query data type of the problematic columns are? (For example, using Power Query inside Power BI or Excel, wrap the original M-query in a call to Table.Schema and look at the "TypeName" column).

[otykier]

The bug I mentioned above has been fixed in 3.0.1. Could you let me know if the issue still persists?

[zenzeinet]

Hi,

From what I've been looking, the problem is on my side. but it would be interesting that it would not. Let me explain:

• I have multiple partitions, and for the same field some of them return INT and others return FLOAT.
• TE3 looks at the data types of the first partition (as far as I could test, not alphabetically but the first in order of adding it to the table), and gets the data types from there. I have managed to have (or not) the data type suggestion change depending on the order of adding/deleting partitions

I understand this is not good design, but it is the way the source data we join is typed. We could unify it in PQ/M steps, but in general prefer to do as little as possible there.

It would be great if TE3 could check all the partitions, and suggest the safest / more general type whenever multiple types are assigned to a column. Also, having a warning that this is happening would be good - specially if strings are implicitly converted.

You can mark this issue as "user error" or "TE3 improvement" as you better see fit :)
BR

[otykier]

I will provide an option for checking all partitions (it might be slower than checking only the first one), and showing a warning if data types do not match between individual partitions. Great suggestion!

[powerjibe]

Hi, I just wondering where to find the link in the application to update the table schema.
Right Click on table has no option for it. I am open the model from a folder. Could that be the reason? KR

[otykier]

Hi @powerjibe. Currently, the Update Table Schema feature is only enabled while connected to an instance of Analysis Services. So if you are opening the model from a folder, you should use the new Workspace Database feature to simultaneously connect to Analysis Services.

There will soon be an update, that allows Tabular Editor 3 to update table schema even when not connected to Analysis Services, but this option will be limited in the same way as it is in Tabular Editor 2.x (i.e. it only works with SQL-partitions against relational data sources - so no support for Power Query partitions).