Append Table as new - different column Types with same column name?

[Anonymous]

Hi All, 

So I came accross this strange behaviour when trying to append a table and then merging this table with another query.

Table 1

Column 1 Type Text

Column 2 Type Any

Column 3 Type Whole Number

Table 2

Column 1 Type Integer

Column 2 Type Any

Column 3 Type Whole Number

Rsults in following Append:

Column 1 Type Any - which makes sense because I append two different column Types

Column 2 Type Any

Column 3 Type Whole Number

Then I merge with an left outer join with a table that also has column 1 but Type Text.

When I then look at the result of the merge - the values from Table 1 that have column 1 Type Text populate properly while all of the other values in the append table column 1 coming from table 2 with type integer are omitted from the merge. 

Is this intended behaviour?

M

[Anonymous · Accepted solution]

Hi @Anonymous,

This is an intended behaviour. Type any means that column may contain any type, but joins respect typisation.

1 does not equal "1", ie number and text aren't equal. You can observe precisely exact behaviour in Excel vlookup - mixing numbers and text that look like numbers make vlookup behave seemingly weird.

In your case you have exactly this numbers mixed with text that look like numbers - hence the column type switches to any as PBI does not take responsibility for converting tyles behind the scene.

Kind regards,
JB

[Greg_Deckler]

Seems odd. Can you post sample data to experiment with?

You could check the Issues forum here:

https://community.powerbi.com/t5/Issues/idb-p/Issues

And if it is not there, then you could post it.

If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".