Forum Discussion

Paul_Cracknell's avatar
Paul_Cracknell
Copper Contributor
May 15, 2022

Power Query Lookup Table Strategy - Advice Needed

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

excel
  • flexyourdata's avatar
    flexyourdata
    Iron Contributor
    May 15, 2022

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

     

Resources