cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ovetteabejuela
Impactful Individual
Impactful Individual

Who should do the work? Excel, M or DAX?

Hi Community!

 

 

I've been meaning to ask as I was actually trying to divide labor by leaving some to Excel and some to DAX or Power Query)

 

Now what would be best? Should I leave everything to Power BI?

 

For instance(1 of many) I have a data soure in time format but is supposedly are durations, to convert, one must multiply it by 24.

At the moment I left that task to Excel to convert them into a decimal prior to loading to PowerBI - but I'm half-hearted because I think Power Query or maybe DAX can do the job(better,faster) - but is this true?

 

Which is better? And maybe in general (not just the instance, I presented) should I leave everything to PowerBI and just leave the Excel data source alone be a "data source" and not become a template?

 

 

Duration ADuration BDuration CDuration DDuration E
9:00:009:00:000:00:000:00:000:06:59
9:00:009:00:000:00:000:00:000:03:58
0:00:000:00:000:00:000:00:000:00:00
0:00:000:00:000:00:000:00:000:00:00
9:00:009:00:000:00:000:00:000:02:05
9:00:009:00:000:00:000:00:000:04:44
9:00:009:00:000:00:000:00:000:01:54
9:00:009:00:000:00:000:00:000:01:55
9:00:009:00:000:00:000:00:000:08:01
0:00:000:00:009:00:000:00:000:00:00
9:00:009:00:000:00:000:00:000:03:01
9:00:006:06:330:00:000:00:000:00:00
9:00:008:56:420:00:000:00:000:00:00
9:00:009:00:000:00:000:00:000:23:09
9:00:009:00:000:00:000:00:000:06:42
9:00:009:00:000:00:000:00:000:01:54
9:00:009:00:000:00:000:00:000:03:21
9:00:009:00:000:00:000:00:000:31:05
9:00:009:00:000:00:000:00:000:27:54
9:00:009:00:000:00:000:00:000:02:41
9:00:008:54:350:00:000:00:000:00:00
0:00:000:00:000:00:000:00:000:00:00
9:00:009:00:000:00:000:00:000:07:55
9:00:008:45:190:00:000:00:000:00:00

 

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @ovetteabejuela

 

My advise is simple and always the same:

- import and massage data by using Power Query aka M in Power BI.

- Create your calculation with DAX.

 

By learning Dax and M you will learn to find the nuances and intersections in the two languages.

 

In your specific case I may give you the typical consultant answer: it depends.

If you want to publish your data to Power BI Service or want to create super, high-class diagramms, then choose Power BI Desktop and Power BI Service.

If your doing a one-time job, then Excel with Power Query and Power Pivot would be your choice.

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Hi @ovetteabejuela

 

My advise is simple and always the same:

- import and massage data by using Power Query aka M in Power BI.

- Create your calculation with DAX.

 

By learning Dax and M you will learn to find the nuances and intersections in the two languages.

 

In your specific case I may give you the typical consultant answer: it depends.

If you want to publish your data to Power BI Service or want to create super, high-class diagramms, then choose Power BI Desktop and Power BI Service.

If your doing a one-time job, then Excel with Power Query and Power Pivot would be your choice.

View solution in original post

Thanks @Anonymous.

I actually had the exact same question about duration measures in my project. 

should I calculate them in power query/M or Power BI/DAX.

What are the pros and cons?

One pro I can see is exposing the logic behind calculations and making it easier to tweak (for example when I use CALCULATE() and want to update the filter), but in that case of the duration the logic is pretty basic and static.

You said:

"

- import and massage data by using Power Query aka M in Power BI.

- Create your calculation with DAX.

"

Where does massaging stop and calculation starts (and derived columns for that matter)?

On which side of that fine line does a duration calcuation stand?

 

Thanks in advance for your inputs?

Anonymous
Not applicable

@osinquinvdm

Oh, that is a tricky question....

Rules of thumbs: calculate measures in DAX!

 

Why? Every table, every columns uses storage, which is still precious.

A measure is calculated on the fly and uses RAM.

A well defined measure (calculation time under 3 seconds!) is always faster and "cheaper" than a column.

@marcorussocan elaborate on this topc for hours 😉

Read "The Definitive Guide to DAX" by @marcorusso and @AlbertoFerrari, watch their videos and learn.

 

btw: a duration calculation should be made in DAX 😉

 

 

 

@AnonymousI actually didn't know that  @marcorusso and @AlbertoFerrari is part of the community. I bought their book - The Definitive Guide to DAX, it's a valuable book! Thanks for that book by the way.

 

As for the duration I was starting to opt for M but now that you've suggested DAX -hmmm...

 

@Phil_Seamark I tried a solution using M, but I ended up with more columns because what I did was:

 - Create a new custom column for each field but now multiplied by 24 so from 5 columns I now have 10 for these durations alone is there a better approach?

@Phil_Seamark & @Anonymous

 

Thank you for your input, those are valuables. I should start to move away from transforming data within Excel specially that this is not a one-time job, this one stays.

 

I'll be posting more questions from here regarding data transformations so I could stop relying on Excel doing it. 1 question coming shortly...

Phil_Seamark
Microsoft
Microsoft

I vote M


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.