Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
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.
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.
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 😉
@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...
I vote M
User | Count |
---|---|
99 | |
87 | |
80 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |