Merging Tables Efficiency Question

[boldwake]

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 product table to the sales table (Brand & Category).   While i hve no problem using relationships and multipple different tables, my users get really confused so I figured for this report it would be easier to just merge / add those the Brand & Categories into my sales table. it takes FOREVER to make simple changes in query editor and and even LONGER to load once I appply the "changes".

I added the columns using the Merge function and set the properties of the table I no longer need (the one with Brand and Category) to not load into the report. 

I have also done it in DAX and it is significanlty faster, so yes i can do it in DAX but I'm really lost why using the merge in the

query editor seriously messes up the pefromance. 

Does anyone know why this is?  Any suggestions on the optimal method for using a "vlookup" type function to add Brand and Cateogrory to the sales table?

Thanks!

[v-ljerr-msft · Accepted solution]

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.

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

[Anonymous]

Late to the party here, but it sure looks like every time I do a merge query, it has to load up all the data into memory again for each merge.  I am hitting the magic combination of suck because one of the tables that I am merging with loads up multiple data files from a Folder data source.  

I am going to switch to a DAX approach but thats a bummer because it sure is easier to use the merge query in the editor.

[b3inct]

I'm having the same issue here..

Not possible to merge tables as this merge and all subsequent steps are taking ages. 

[bjbecker]

This thread hits exactly on my problem! Did anyone ever figure this out?

[BhaveshPatel]

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]

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]

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.