DAX Fridays! #106: Power Query or DAX?

If you already know, how about you share your thoughts here? If not, then I will share my thoughts in the topic.

There are no perfect answers, only good questions ;)

Happy Friday!

SUBSCRIBE to learn more about Power and Excel BI!

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

▲▲▲▲▲▲▲▲▲▲

Our PLAYLISTS:

************

What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Twitter ► @curbalen, @ruthpozuelo

#daxfridays #curbal #dax #powerbi #mvp

[McRyach]

Perfect, thank you very much. I didn't have a question which language to learn. But I had a question When to use *M* and when to use *DAX.* This video answers it perfectly.
*Use M to clean your data*
*Use DAX to ask questions*

[zzzzzzzzzzzzzzzz]

PQ first is a great tip. I think of it like this.
PQ is like a car's starting engine, without the start, the car won't run. If the wires are disjointed, your car won't work properly

[zbatevp-vlogs]

I would totally agree, when i was a total beginner i was first attracted to DAX and didn't see much importance on Power Query. But as I go along the importance switched personally i think the cleaner the data (Power Query) the easier it is to used DAX. My two cents as well.

[joseagundis]

Me gustó mucho tu explicación y creo que el proceso de aprendizaje es Power Query-Dax-Lenguaje M y lo hacemos intuitivo sin pensarlo al menos eso estoy experimentando; asi tambien lo experimente con Excel-VBA. Concluyendo que no puedes usar Dax sin que tus datos hayan pasado por una limpieza y no puedes usar lenguaje M sin antes saber Dax y Query; tal como cuando no pude escribir una macro sin entender Excel. Esto pienso yo. Saludos.

[hcedenop]

Ruth, thank you, this is so effective and thoughtful advise you gave!, Power Query 1st...

[nachomiranda]

You are absolutely right. I am suffering the maintenance of my first PBI reports built with almost DAX only, creating calculating columns and even tables by copying columns from other tables .... 😰. They make me feel very sad about myself ....
Now, after some time devoted to learn PowerQuery, things are easier and have much more sense.
I’m not sure if I will do the next step and come into M. So far, I have only needed a few simple functions. We’ll see
I’m really looking forward your Monday video about PBI data flows.
Thanks

[ChequeWriter]

VBA will always reign as king in my hear!

[zhaoyong]

i totally agree. PowerQuery then DAX.
I guess i'm lucky to pick up M is for Data Monkey first before moving toward DAX. These days I'm seeing a lot of people in my area of work using DAX to create the table (Which make the visual in PowerBI extremely slow) whereby PQ would do the trick.

[mehdihammadi]

I totaly agree with you Ruth, in all project you will have at least to Extract Data but not necessary create measures and the general advice from PBI experts is, if you can do it with Power Query rather than DAX, then do it in Power Query

[tubez]

The other day I created a bunch of measures only to find you that Visuals and Measures don't get along. Totals were wrong and calculations were complexed. I ended up removing DAX queries and move them back to MQuery. I only left the DAX formulas that interacted with the visuals. Thank you.

[sopauljauck]

I agree with your reasons and concept but I'm a little confused because of other tutorials telling me that PQ will impact performance. Thoughts?

[HachiAdachi]

Thank you for your insight, Ruth. Is there a performance difference between M and DAX? I often feel that Power Query is slower in an equivalent task when comparing to DAX, so I have been trying to do as much as possible in DAX (within my limited DAX skills). My sentiment on the performance is purely based on my unscientific anecdotal personal samples, so I would love to hear your take on that.

[xela]

How to reduce the use of calculated columns?. When me need to calculate a measure we apply dax or in the data source.

[rajkumarbharathi]

Ranking in bad in power query. if you could rank by groups in Power query, it will be too good

[joepepper]

Definitivamente muy ilustrativo tu video respodiste al 100% el flujo mas adecuado para utilizar M, Dax y power bi. Mi mundo es y a sido excel definitivamente hay que moverse a uso de herramientas mas eficientes.

Tengo un imteres muy grande en seguir tus recomendaciones. He empezado con power bi a traves del excel pero me resulta un poco confuso ya que quiero verlo como excel, es como saber ingles y vas a aprender frances y en este proceso nuestro switch sigue activando el ingles....

Saludos desde la ciudad de Monterrey en este pais maravilloso llamado Mexico.

Por cierto, tambien te deseo un maravilloso fin de semana.

[yaminkerkoub]

I clean my data with SQL first, then M if it is necessary then enjoy with Dax measures.

[aytugokumus]

Hi Ruth
Thanks for nice video. Really helpful.
I have a question & problem. Would be great if you can explain how to do that..
Question ;
I have an excel table for projects, about 100.000 rows, with 10 columns that specifies the project specs. Question is ; In Power BI, i will choose one project from table then i would like to find most similar projects from main excel table according to similarity based on column data (each column will have weighted similarity factor & column context can be number & text).
And then i will filter & list them based on more similar project to less similar project by similarity score.
?

[jasonwoodward]

Thanks Ruth.

As I have some SQL and basic SSIS skills, is it worth learning Power Query\ M language or should I focus my efforts on just Power BI and Dax?

[thesatwikshenoy]

Thanks for the tips. On my way to begin M Language :)

[dasrotrad]

Great advice Ruth! AND a beautiful explanation. I must admit, it was so rewarding to start with Power BI and DAX, but it brought enormous frustration. I had studied context and thought my formulas were spot on. But, they didn't work. It was confusing. I was confused about context. Why isnt this working?? My data model was wrong. These pieces all have to fit. As I grapple with all of these pieces, I became aware that power query is needed to not only clean the data, but restructure the tables to prepare them for data modeling. Yes, it's great to jump in and see your DAX results in the great visuals that are available in Power BI. Sometimes they work and sometimes they don't. You can't skip the basics. You have to work with clean data and you have to have the data model correct. ...then, DAX is ready for you! I think I am starting to see the forest. Thank you Ruth and thank you to all the other MVPs who, like you, put so much effort into guiding through this amazing set of tools MSFT has created. It is an exciting learning adventure.