LOOKUP VALUE in direct query

[admin_xlsior]

hi,

is LOOKUPVALUE not supported in Direct Query ?

I have two table (from SQL Direct query) which both no relationship.

But I want to get the value from table A to table B.

I try to add calculated column in table B, but there is no such funtion if looking at intelisense.

Thanks,

[Anonymous]

Hi @admin_xlsior 

Try to create the relationship.

It should work.

Good Luck!
A

[admin_xlsior]

Hi,

For unknown reason, I cannot put relationship to that table, everytime my Power BI freeze.

FYI, the table I wan to to relate (or the loookup table)  is having a binary column (it's a jpeg file), would that be a problem ?

I also get the data not in the "normal way", because somehow I need to run a query in the M language, because I need to convert the binary column from SQL server to text during the catch, like this :

let

    Source = Sql.Database("---myAzureServer--", "--myDB--", [Query="select distinct dataareaid, cast('' as xml).value('xs:base64Binary(sql:column(""image""))', 'varchar(max)') as [Image] from COMPANYIMAGE", CreateNavigationProperties=false])
in
    Source

Suspected this is also the cause it is freeze when I want to put relationship to other table. Hence I decided not to put a relationship but use LOOKUPVALUE function.

thanks.