cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
boldwake
Frequent Visitor

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 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!

 

1 ACCEPTED SOLUTION
v-ljerr-msft
Microsoft
Microsoft

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.Smiley Happy

 

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

View solution in original post

7 REPLIES 7
v-ljerr-msft
Microsoft
Microsoft

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.Smiley Happy

 

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

View solution in original post

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.

I'm having the same issue here..

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

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

BhaveshPatel
Community Champion
Community Champion

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.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

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
Resident Rockstar
Resident Rockstar

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.

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.