data models - best practice - loading the data

[jvantassel]

Hi all,

I'm learning power query and the use of the data model and wondering what the best practice might be.
I brought in several "lookup" tables and loaded them to tables in my existing workbook in addition to adding them to my data model.
Next I have my transaction data currently 4,500-ish rows.
What is best practice ...
1. is it best to create connection only and add to the data model
2. load to a table in the existing book, and add to the data model
3. not add to the data model (this wouldn't make sense to me)

and...
Do I use merge queries during the transform step to add fields from my lookup tables, or do I bring in my transaction table then create the relationships in diagram view then create new queries?
or possibly do both, use the merge query during the transform, then create a connection only, add to data model, and create relationships in diagram view.

Thoughts??

 

[GraH]

Never load the queries to tables if those are to be re-used in the data model. Use connection only and set load to data model on.
Depending on the required outcomes it might be needed to enrich data inside the Power queries. However as a rule of thumb using relationships inside the data model is the way to go for lookup tables.
Creating a datamodel is not easy. Good luck with that. I can only advice to ask very precise questions on fora and read books on the topic.

 

[jvantassel]

G.,
Thank you for your reply. That's similar to how I built Access apps some time ago. I would store the data in the backend, and then update the GUI as things progressed adding new fields as needed on the backend.