Closed zenzeinet closed 1 year ago
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).
The bug I mentioned above has been fixed in 3.0.1. Could you let me know if the issue still persists?
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 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
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!
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
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).
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.