Join direct query tables on a newly created/calculated column

[sam245gonsalves]

I have two tables that I get in via DirectQuery. 

Table 1 contains complete postal codes but the formatting is all off

Table 2 is a clean table that contains postal codes too.

Since Table1.Postalcode is not really usable untill I format it. I needed to know if there is a way I can create a new column in table 1 & then join both.

I cannot use PowerQuery to create a new column because both tables are DirectQuery tables.

Any suggestions please?

[v-deddai1-msft · Accepted solution]

Hi @sam245gonsalves ,

You can add calculated column for your table1. But there are some limitations:  

Calculated columns are limited to being intra-row, as in, they can only refer to values of other columns of the same table, without the use of any aggregate functions. Additionally, the DAX scalar functions, such as LEFT(), that are allowed, are limited to those functions that can be pushed to the underlying source. The functions vary depending upon the exact capabilities of the source. Functions that aren't supported aren't listed in autocomplete when authoring the DAX for a calculated column, and would result in an error if used.

And if you want  an accurate formula for calculating columns, please show us sample data for reference.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[amitchandak]

@sam245gonsalves , You can use power query and Dax new column for simple transformation.

Few on them discussed in series - https://www.youtube.com/watch?v=My0bLn9voo4&list=PLPaNVDMhUXGbKatyDdOhGbTL3xW2Xy6pA

Can you share some sample data to check. But would be better if you can do some clean up at DB level