Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rogletree
Helper III
Helper III

Transform data in power query vs. dax

Not really sure if the title accurately describes what I'm asking about, but basically I'm just wondering what the pros/cons are to transforming data in power query vs doing it with DAX.

I prefer power query because I'm a bit more familiar with the M code, and I find the table transformations to be a little more intuitive. I have a data model that I update daily; every day I add in the previous day's data, which is usually less than a thousand rows of information.

I have it to where I have a folder that Power BI takes the data from, so I just save an excel file into the folder and I only have to do the transformations within Power BI one time and it takes care of it every other time.

But it seems like whenever I open my report on Monday morning it takes forever to load everything. With my data model I got my lookup tables by duplicating my main table several times then just removing the unecessary columns and removing duplicate values. Is this considered bad practice? It allows me to quickly and easily do what I need but I'm wondering if this is what's causing my program to run so slow due to it essentially having to do the transformations every time it's loaded (is this even true??).

I guess I'm just wondering if doing too many table duplications and stuff in power query would have a drastic impact on how long it takes for reports to load up. Thanks for reading.

3 REPLIES 3
amitchandak
Super User
Super User

@rogletree , Duplication of the data in power query that gives me worry. Dax solution can be slower than M but it might overall give much more gain in some cases.

But I think you have to checkout with what you need.

Selectcolumns , calculatetable, summarize should help you out. Try to avoid crossjoin as far as possible.

 

This is good blog on joins -https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

@amitchandak thanks for the reply. What exactly do you mean when you say that DAX can be slower than M? Writing the code itself is slower? Or the time it takes Power BI to do the calculations is slower?

Hi @rogletree ,

 

As for "Slow",all depends.

Check below blog for the differences between dax and power query:

https://www.sqlbi.com/articles/comparing-dax-calculated-columns-with-power-query-computed-columns/

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.