Relation between Direct Query Table and Import table stop working

[cooler1978]

Hello.

I have two tables, comes from the same MSSQL database, one table is in Direct Query , second is in the Import Mode , some time ago everythung was working fine. Suddenly (probably i did something wrong with Power BI or  certain report) it stops working at all.

Relation between table is still there, no error or issues with it. 

Any idea where to look at?

Relation works, i checked manually for few items - you can find them in both tables, so at least 4-5 items must be, assume all of them there

Simple visual - table once you add ID from both tale shows nothing at all.

[amitchandak · Accepted solution]

@cooler1978 , this should work.

Check is there a data type change for import or direct query table. Open power query and check there too.

Check the text for white space, trim the column.

[cooler1978 · Accepted solution]

Also problem is going out, the moment you change Direct Query mode to Dual or Import, and it's back the moment you turn out to Direct Query mode.

So the problem is for sure how the data (data types) is processing by Power Query in different modes, and from different table or data type in DB, in my case it's MSSQL DB.

So you have two option, as far as i understood - either you switch to Dual or Import mode whether it's possible or you add in Power Qury step with deleting spaces. 

That's it.

But if somebody know better way or explanation - warmly welcome to post it)

[amitchandak · Accepted solution]

@cooler1978 , this should work.

Check is there a data type change for import or direct query table. Open power query and check there too.

Check the text for white space, trim the column.

[cooler1978]

@amitchandak Yes, you was right and i was right as well.

Strange thing is following , if i add im PQ step with "Delete spaces" everything stats working, but in both tables if i am trying to find spaces - PQ shows empty table, so there is no spaces.

What i think - i catch stage behaviour - when i add step "Delete spaces", i think in Direct Query table something going different way with data type, because before doing this step i see data from Direct Query table in visual with strange brace, i alway thought that this braces there, because it's in direct query mode 

And nothing working in this case

But after adding step with "Delete spaces" into Direct Query table (and nothigh else, no changing data type, nothing) i see the same table in the same visual in Power BI as following

and relation starts working perfect after this step. 

In Direct Query mode i cannot see data in Power Bi so i cannot check how they are coming, in what type, in Power Query, interface shows before this step and after the same picture - column is "Text" type

[cooler1978]

If you mean relation column, there is no empty cells, there is no space and no changing type, both columns are text type in Power Query.

One table, imported, comes from DB as is, second, direct query,  has some transforamtion, but no changing type:

let
Source = Sql.Database("192.168.1.33", "bi"),

Crossgroups = Table.SelectColumns(Source{[Schema="dbo",Item="Crossgroups"]}[Data],{"ID"}),
count = Table.RowCount(Crossgroups),

Pictures = Table.SelectColumns(Source{[Schema="dbo",Item="Picter"]}[Data],{"ID","IDobject","URLКартинки_FullSize"}),
count1=Table.RowCount(Pictures),

Filter = Table.Join(Table.RenameColumns(Pictures,{{"ID","Pictureid"}}), {"IDobject"}, Crossgroups, {"ID"}, JoinKind.Inner),
count2 = Table.RowCount(Filter),
Table = Table.RemoveColumns(Filter,{"ID"})

in
Table