Power Query Lookup Table Strategy - Advice Needed

[Paul_Cracknell]

Hi, all. I have a large flat file and I am seeking advice on how to best create lookup tables. Currently, I am cleaning up the flat file and using “Duplicate” to create my lookup tables. This is slowing my model down. Each time I use “Duplicate” to create another lookup table, it takes longer for the model to process the data. This flat file I am working with has 6 different lookup tables. By the time I get to my sixth lookup table, the model is really slow.

Is there a better, more efficient way to create lookup tables? I have thought about creating separate queries for the lookup tables and then connecting to the separate files.

Any advice would be greatly appreciated.

Thanks,

Paul

[#2]

@Paul_Cracknell 

Can you add an example file?

Some considerations:

1. How many rows in the flat file?
2. How many transformations between Source and Duplicate?
3. What are the cardinalities of the lookup tables?
4. Are you building this model in Excel for us with Power Pivot  - or is this Power Query question actually for use in Power BI (or something else)?