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*
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
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.
Exactamente! Un pequeño comentario: se puede aprender M sin saber nada de DAX pero si vas a hacer informes en power bi, es necesario aprender lo básico de DAX antes de meterte con M. Saludos! /Ruth
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.
Exactly! I couldn’t have said it better! The cleaner the data and the better the model (relationships and tables), the easier DAX gets, well said! Thanks for sharing your experiences:) /Ruth
me gusta ese mensaje: "cuanto más limpios son los datos (power query), más facil es utilizar DAX". Ustedes son unos capos como decimos en Perú. un abrazo
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
I feel your pain!! I started with Power query early, but that didn’t save me from the calculated columns nightmare 😂😂 Hey! Look at it this way, you need to crawl before you can walk right? Happy Friday and thanks for sharing your story! /Ruth
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.
Depending on your data and how the queries are created, Power query can be slow, and if that happens, there are techniques you can apply to make it faster. The same applies to DAX. I should do a series on M performance or expand the M course to bring some light to this. Great question by the way! /Ruth
@@CurbalEN Hi Ruth, i have this question too! For example there are some dimtables that in some models i did it first with power query, in others i did it with dax as calctables. So i wonder in terms of refreshing performance, what would be better, do calculated columns and tables with power query or do it with dax? What do you think?
Performance on a model depends on many factors, so it is not possible to say one or the other without having a case in mind, but best practice is; power query for shaping and preparing the data and tables and DAX for asking questions to your data. /Ruth
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.
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?
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. ?
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.
I see it also all the time! Wish I could spread this message to every power bi user so they don’t have to go through that! Thanks for sharing your story :) /Ruth
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
Thanks for sharing your experience! I think calculations should be done in DAX as vertipaq will manage those better, but when you are a beginner...everything is allowed until the model bites you back! Happy Friday! /Ruth
Ruthhhhhhhh!!!! 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.
Gracias Joeeee ;) Te entiendo, yo empecé también de Excel y pretendí hacerlo todo como lo hacía en Excel, pero poco a poco me di cuenta que no, no se puede... Es un proceso, caminar antes de correr... no te preocupes, con perseverancia lo vas a conseguir!! Buen fin de semana!! /Ruth
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.
Thanks for sharing your story! Success stories helps beginners keep fighting, you guys are awesome! I agree 100 % with you: first power query, then modeling and then DAx, in that order. Have a great Sunday! /Ruth
I don't know if you address this in you M Course, but in thinking how to prepare the data model, I see the need to flatten the 1:1 relationships and eliminate the snowflake schema. Do you address strategies to re-design many-to-many relationships into 1:Many relationships?
Yes I hope so too :) I wanted to ask you this since a while - is there any chance if you'll explore other Reporting tools in parallel? Like the Google Data Studio. It's new and almost no material online. Would be great if you considered making some videos in the future :) Or maybe consider implementing python or R visuals in Power BI. I see so many possibilities still not explored well. Just my 2 paise as they say in India :) All the best! Big fan!
Yes, I am thinking about it as I have been asked before. This year is too tight to do such thing, but next year I might give it a go! What I can do, is do a test video this year, how about that! Thanks for watching!! /Ruth
@@CurbalENno this video(i meanDAX Fridays! #106: Power Query or DAX?); because I started with DAX after I realized that I need more power query than DAX because i work a lot with EXCEL (but like you said 1power query 2DAX and finally M language so no mistake now :)
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*
You got it!!
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
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?
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.
Exactamente! Un pequeño comentario: se puede aprender M sin saber nada de DAX pero si vas a hacer informes en power bi, es necesario aprender lo básico de DAX antes de meterte con M.
Saludos!
/Ruth
Ruth, thank you, this is so effective and thoughtful advise you gave! , Power Query 1st...
Thanks!
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.
Exactly! I couldn’t have said it better! The cleaner the data and the better the model (relationships and tables), the easier DAX gets, well said!
Thanks for sharing your experiences:)
/Ruth
Great words: "the cleaner the data (Power Query) the easier it is to used DAX"
We should put that in a pop up message when you open power query!! That would be something!
/Ruth
Yes, that would be great :)
me gusta ese mensaje: "cuanto más limpios son los datos (power query), más facil es utilizar DAX". Ustedes son unos capos como decimos en Perú. un abrazo
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
I feel your pain!! I started with Power query early, but that didn’t save me from the calculated columns nightmare 😂😂
Hey! Look at it this way, you need to crawl before you can walk right?
Happy Friday and thanks for sharing your story!
/Ruth
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.
Depending on your data and how the queries are created, Power query can be slow, and if that happens, there are techniques you can apply to make it faster. The same applies to DAX.
I should do a series on M performance or expand the M course to bring some light to this.
Great question by the way!
/Ruth
@@CurbalEN Hi Ruth, i have this question too! For example there are some dimtables that in some models i did it first with power query, in others i did it with dax as calctables. So i wonder in terms of refreshing performance, what would be better, do calculated columns and tables with power query or do it with dax? What do you think?
Performance on a model depends on many factors, so it is not possible to say one or the other without having a case in mind, but best practice is; power query for shaping and preparing the data and tables and DAX for asking questions to your data.
/Ruth
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.
That should not be the case, but glad you got it sorted!
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?
I would say Power Query, then DAX and later if you need it, M in that order.
Hope it helps!
/Ruth
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.
?
thank you very mcuh for your advice.
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.
I see it also all the time! Wish I could spread this message to every power bi user so they don’t have to go through that!
Thanks for sharing your story :)
/Ruth
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
Thanks for sharing your experience!
I think calculations should be done in DAX as vertipaq will manage those better, but when you are a beginner...everything is allowed until the model bites you back!
Happy Friday!
/Ruth
yes sir !!!; 100% right :)
Yeah!
Thanks for the video! Thumbs up!
Thanks! Glad it was useful :)
/Ruth
VBA will always reign as king in my hear!
Thank you xxx
Great explanation Ruth. Thanks
You welcome! I hope this aids you in avoiding my mistakes !
/Ruth
How to reduce the use of calculated columns?. When me need to calculate a measure we apply dax or in the data source.
Hi Alex,
Do data transformations in power query and mathematical calculations in Dax as measures as much as you can.
/Ruth
Ruthhhhhhhh!!!!
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.
Gracias Joeeee ;)
Te entiendo, yo empecé también de Excel y pretendí hacerlo todo como lo hacía en Excel, pero poco a poco me di cuenta que no, no se puede...
Es un proceso, caminar antes de correr... no te preocupes, con perseverancia lo vas a conseguir!!
Buen fin de semana!!
/Ruth
I clean my data with SQL first, then M if it is necessary then enjoy with Dax measures.
Yes! That is even better! Perfect if your can clean the data at the source :)
Thanks for sharing!
/Ruth
Ranking in bad in power query. if you could rank by groups in Power query, it will be too good
Thanks for the Path 🙂
It worked for me, hopefully for you too!
/Ruth
Very good explanation!
Thanks! How was your journey? Did you started with Power Query or DAX?
/Ruth
@@CurbalEN I started with Power Query, and data cleaning.
Oh! Great ! You did it the right way!
Thanks for sharing :)
/Ruth
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.
Thanks for sharing your story! Success stories helps beginners keep fighting, you guys are awesome!
I agree 100 % with you: first power query, then modeling and then DAx, in that order.
Have a great Sunday!
/Ruth
@@CurbalEN I have signed up for your M course! I so excited!
Cool!! Let me know what you think when you finish it!
/Ruth
I don't know if you address this in you M Course, but in thinking how to prepare the data model, I see the need to flatten the 1:1 relationships and eliminate the snowflake schema. Do you address strategies to re-design many-to-many relationships into 1:Many relationships?
@@CurbalEN Wilco!
I totally agree
Me too ;)
Thanks for the tips. On my way to begin M Language :)
M is a lot of fun! You are going to enjoy it :)
/Ruth
Yes I hope so too :) I wanted to ask you this since a while - is there any chance if you'll explore other Reporting tools in parallel? Like the Google Data Studio. It's new and almost no material online. Would be great if you considered making some videos in the future :)
Or maybe consider implementing python or R visuals in Power BI. I see so many possibilities still not explored well. Just my 2 paise as they say in India :)
All the best! Big fan!
Yes, I am thinking about it as I have been asked before.
This year is too tight to do such thing, but next year I might give it a go!
What I can do, is do a test video this year, how about that!
Thanks for watching!!
/Ruth
Would do :) thank much Ruth. Cheers!
Hi Ruth
If u can possibly plz share ssas tabular Cub vedios , because power bi using tabular cubes .
Thanks advance
The guys at pragmatic works have a playlist , have you seen it?
m.ruclips.net/p/PLcwrIWK7WBcTjHS4_BC6w7e2dnpEgfOaI
/Ruth
@@CurbalEN yes but if u provide topic Wisely and senarios it will use for me. Thanks
Thanks but SSAS is outside my area. Have you seen this :
www.sqlbi.com/p/ssas-tabular-video-course/
/Ruth
Check this source out also :
www.sqlauthority.com
/Ruth
I hope you did it before :), nevertheless your DAX Friday is great!!!
Did what before? Calculated columns?
And thanks!
/Ruth
@@CurbalENno this video(i meanDAX Fridays! #106: Power Query or DAX?); because I started with DAX after I realized
that I need more power query than DAX because i work a lot with EXCEL (but like you said
1power query
2DAX
and finally M language
so no mistake now :)
That was my journey too... but hey! Making mistakes is learning also!
/Ruth
@@CurbalEN yes indeed, and with a professional you save time :)