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

[ovetteabejuela]

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 A	Duration B	Duration C	Duration D	Duration E
9:00:00	9:00:00	0:00:00	0:00:00	0:06:59
9:00:00	9:00:00	0:00:00	0:00:00	0:03:58
0:00:00	0:00:00	0:00:00	0:00:00	0:00:00
0:00:00	0:00:00	0:00:00	0:00:00	0:00:00
9:00:00	9:00:00	0:00:00	0:00:00	0:02:05
9:00:00	9:00:00	0:00:00	0:00:00	0:04:44
9:00:00	9:00:00	0:00:00	0:00:00	0:01:54
9:00:00	9:00:00	0:00:00	0:00:00	0:01:55
9:00:00	9:00:00	0:00:00	0:00:00	0:08:01
0:00:00	0:00:00	9:00:00	0:00:00	0:00:00
9:00:00	9:00:00	0:00:00	0:00:00	0:03:01
9:00:00	6:06:33	0:00:00	0:00:00	0:00:00
9:00:00	8:56:42	0:00:00	0:00:00	0:00:00
9:00:00	9:00:00	0:00:00	0:00:00	0:23:09
9:00:00	9:00:00	0:00:00	0:00:00	0:06:42
9:00:00	9:00:00	0:00:00	0:00:00	0:01:54
9:00:00	9:00:00	0:00:00	0:00:00	0:03:21
9:00:00	9:00:00	0:00:00	0:00:00	0:31:05
9:00:00	9:00:00	0:00:00	0:00:00	0:27:54
9:00:00	9:00:00	0:00:00	0:00:00	0:02:41
9:00:00	8:54:35	0:00:00	0:00:00	0:00:00
0:00:00	0:00:00	0:00:00	0:00:00	0:00:00
9:00:00	9:00:00	0:00:00	0:00:00	0:07:55
9:00:00	8:45:19	0:00:00	0:00:00	0:00:00

[Anonymous · Accepted solution]

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.

[osinquinvdm]

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]

@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 😉

[ovetteabejuela]

@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?

[ovetteabejuela]

@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]

I vote M