Forum Discussion
BEST PRACTICE: How much data cleanup to do in DAX vs. Power Query?
As I get a little more understanding of how to work with Power BI, I'm seeing that I can do many things in DAX as well as Power Query. Question for you Power BI Professionals: - How much cleanup (...
Generally, Greg_Deckler is right.
I'm of the opinion that you should prepare data for analysis in the right tool. DAX is not this tool. After all, it's a Data Analysis eXpressions language for a reason. A good example: Would you want to do machine learning in SQL? Well, that's what I thought.
Power Query is a data mashup tool and thanks to the mechanism of query folding, it's intelligent enough to push data transformations into the source most of the time (sometimes this requires also the coder to be intelligent). Power Query is SO powerful that it can do ANY transformation of data you can think of and can do it really fast. For this, however, you have to know M (the PQ language) and know how to write optimized code. M is a functional language with its own type system, its own rules, its own quirks and curiosities. You have to learn them to know how to make code lightning fast and maintainable (documenting individual steps, for instance, is one good practice). But it's the same story with any language, DAX included.
Last thing... If you leave blanks in your dimensions, this will not only be ugly for the end user. It will also make code more complex and thus slower. Therefore you should always make sure you do not leave blanks in there and display some sensible labels for conceptual blanks. Fact tables, on the other hand, can have BLANKS in their measurements (not in dimension keys, though) because these are treated as 0 by DAX arith functions and should never be exposed to the end user. All calculations should always be done through measures and measures only.
Violation of the ideal will always happen, of course, but it 's good to give good reasons for such violations so that the next person does not curse you (and under their nose).
Best
D
I'm of the opinion that you should prepare data for analysis in the right tool. DAX is not this tool. After all, it's a Data Analysis eXpressions language for a reason. A good example: Would you want to do machine learning in SQL? Well, that's what I thought.
Power Query is a data mashup tool and thanks to the mechanism of query folding, it's intelligent enough to push data transformations into the source most of the time (sometimes this requires also the coder to be intelligent). Power Query is SO powerful that it can do ANY transformation of data you can think of and can do it really fast. For this, however, you have to know M (the PQ language) and know how to write optimized code. M is a functional language with its own type system, its own rules, its own quirks and curiosities. You have to learn them to know how to make code lightning fast and maintainable (documenting individual steps, for instance, is one good practice). But it's the same story with any language, DAX included.
Last thing... If you leave blanks in your dimensions, this will not only be ugly for the end user. It will also make code more complex and thus slower. Therefore you should always make sure you do not leave blanks in there and display some sensible labels for conceptual blanks. Fact tables, on the other hand, can have BLANKS in their measurements (not in dimension keys, though) because these are treated as 0 by DAX arith functions and should never be exposed to the end user. All calculations should always be done through measures and measures only.
Violation of the ideal will always happen, of course, but it 's good to give good reasons for such violations so that the next person does not curse you (and under their nose).
Best
D
Great insights Anonymous ! You've given me many things to consider, look up and understand. Thank you.
(as an aside, and ONLY because I came across it the other day... yep... "they" are doing ML with SQL now 🙂 )
- They really don't. They run Python or R scripts inside SQL Server. That's not doing ML in SQL.
Best
D
