Direct query issue

[Anonymous]

Hello folks,

I am new to power bi and i went along with the idea that direct query would help my users to be self sufficient. (Real time data)

now that i am working direct query i feel the downside of not being able to clean my tables. Exemple: there are two addresses stored in one table under the same field. Its marked with an identificier to identify the difference. 

Also i have an dossier table where a persoon could have 1 or more dossier id’s. 

The persoon is Unique in the person table and a bridge tabel connects the dossier and person table.

the probleem occurs when i insert a field from the adres table, because someone could have 2 adresses and 2 dossiers for 1 person. So i made the address to the person table Many to many in both ways. The other tabels contact through 1 to many.

In a table visual i get alot of empty cells on street and housenumber. On a person with 2 adresses, i get 4 rows where two are filled and two empty.

its terrible, because i cant extract any correct numbers in a visual from these tables.

so i thought about mixend mode, import and direct query so that i can make adjustment to the tables that have duplicaties. But does my report losse it dynamical direct query functionality?

What can i do to work with direct query and also be able to correct duplicates?

in access or cognos there is a possibility get query results and then work with it in another table.

Is there a possibility in power bi that i could transform a direct query into a import query. Use that import query for my visuals and that tee direct query Will alwalys update my imported query?

hope someone can help me out.

spelling errors may occur in my question because i get auto corrcted on my dutch computer. Sorry in advance.

Kind regards,

Peter

[luisrh]

doing many to many both ways on your modeling will cause performance problems on directquery.  It may really make more sense for you to use staged data - depending on how 'fresh' the data needs to be you can do ensure your data refreshes happen so that the users are happy with how current the data is.   Having an indicator of when a report was last refreshed allows the customer to see that.  If this is purely a PBI desktop use case,  the user can decide when to refresh.   Directquery sometimes becomes a larger problem given you can't do a lot of the flexible things that the import model gives you.    Another option is to look at storing your data in a SSAS model - there you have more options around aggregations,  etc..

Good luck!