Forum Discussion

boldwake's avatar
boldwake
Frequent Visitor
9 years ago
Solved

Merging Tables Efficiency Question

PowerBI SuperUsers,   I have a PowerBI filw with two tables pulled from SQL   1) Sales Table 2) Product Table   I"m trying to figure out the most efficient way to merch 2 columns from the prod...
Need Help
Tips and Tricks
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi boldwake,

     

    According to your description, I would suggest you to keeping doing it in DAX for performance purpose. By this way, you can keep making use of the physical relationships to get best performance and the best use of the VertiPaq engine.:smileyhappy:

     

    In addtion, another possible solution is to use the LOOKUPVALUE function. Using LOOKUPVALUE you can denormalize the Brand & Category directly in the Sales table, by defining a new calculated column in Sales table. This solution can make the code simple to author.

     

    Regards

BhaveshPatel's avatar
BhaveshPatel
Super User
9 years ago

You should check whether your query folding is getting done correctly. You can right click the individual Query steps and click on the native Query option. It will show you the SQL Statement in the dialog box. 

 

PowerQuery pushes back some of the operations to the database instead of doing in the Query Editor. This is called Query Folding and For getting better performance, You should check whether your query is getting folded correctly. 

 

Custom SQL Statements for pulling the data from SQL Server does not support Query folding.

  • boldwake's avatar
    boldwake
    Frequent Visitor
    9 years ago

    Thanks but I should have been more clear.  The tables I'm joining are text output files that were just pulled from SQL,  I am not directly pulling data into PowerBI through SQL.   I just have two text files that have a relationship / common key and I can't figure out why merging 2 columns from the table I dont really need much from is casuing it to take forever to load.   Unfortunately, I can't merge these two tables in SQL becuase both SQL queries already have a lot of joins and the system just kills my query for taking up too much bandwidth if i try and make them into a single text file before brining them in to powerbi.  

     

    What is the best / most efficient / won't kill my processing time every time i make a small formatting change or refresh to join two imported text file tables in PowerBI

     

     

    • Baskar's avatar
      Baskar
      Resident Rockstar
      9 years ago

      cool,

       

      me too facing the same prob while merging. and even file size also increasing much more look at your file size.

       

      my suggestion is go with DAX.