Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cooler1978
Regular Visitor

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 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?

cooler1978_0-1640083924992.png

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

cooler1978_1-1640084145795.png

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

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@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.

 

View solution in original post

cooler1978
Regular Visitor

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)

View solution in original post

4 REPLIES 4
cooler1978
Regular Visitor

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
Super User
Super User

@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.

 

@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 

cooler1978_0-1640089920099.png

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

cooler1978_1-1640090328170.png

 

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_2-1640090499700.png

 

 

 

 

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

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.