Forum Discussion
Impactful IndividualWho 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 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 |
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.
6 Replies
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.
- 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...
- osinquinvdm
Advocate II
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?
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 ;-)
- Phil_Seamark
Microsoft Employee
I vote M
