Forum Discussion

cooler1978's avatar
cooler1978
Regular Visitor
4 years ago
Solved

Relation between Direct Query Table and Import table stop working

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 som...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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's avatar
    cooler1978
    4 years ago

    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's avatar
amitchandak
Super User
4 years ago

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's avatar
cooler1978
Regular Visitor
4 years ago

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