Power BI Group By - Using Query Editor, DAX, and Some Magic!✨
Vložit
- čas přidán 4. 08. 2024
- We’ll show you how you can Group By data in Power BI using 3 easy ways.
👉 Download Power BI File in Video at web.learnpowerbi.com/download/
We’d cover the 3 different ways you can use Group By to summarize your data:
• Group By Using Query Editor
• Group By Using DAX SUMMARIZECOLUMNS
• Group By Using some Power BI Magic 😊
We’d compare the 3 different ways and see which one works the best.
We’d also be sharing a Bonus Tip for using Group By in Query Editor.
➔ Links mentioned in the Video
• Power BI Tutorial for Beginners
• Power BI Tutorial for ...
================================
👉 FREE Power BI Step-by-Step Tutorial web.learnpowerbi.com/tutorial
👉 Download Accompanying PBIX Files for Video at web.learnpowerbi.com/download/
✅ Subscribe to always get my latest videos: czcams.com/users/powerbipro?su...
🔴 Join me Fridays for Talk Power BI LIVE czcams.com/users/PowerBIProlive
👪 Join our Talk Power BI Insider's Club www.TalkPowerBI.com
===Most Popular Playlists===
• Power BI Beginner Tutorial: goo.gl/KRRkPG
• Ultimate Calendar: goo.gl/pyki4K
• Power BI Data Modeling and more: goo.gl/WNtJFv
⚡Power On!⚡
-Avi Singh
www.LearnPowerBI.com
www.AviSing.com
#PowerBI #PowerBIHowTo #PowerBIPro
Great Overview, Avi! One tip about "count rows" for checking yourself. You dont need to use "Group by" for that. There is the whole button for that - "Count Rows", under buttons Transpose and Reverse Rows.
Avi great job. I love the way you show the different ways to arrive at the same solution. Super helpful.
Thanks for the explanation Avi, this magic in the end is what the databenders have to master!
Great Video Avi! Offering comparison really helps understand the differences
Many thanks on the video. Was getting all tangled up with IF() Calculate() and summarize ()
Glad it helped!
This is so helpful, thank you. I was struggling with the sum and grouby but I have managed to get through after watching this video
I don't usually comment but today I will. First off, I love your book and have used it for the past few year. Secondly, this video with the Groupby and Merge query was the solution I needed and spent all day on useless websites and videos. Your training was perfect, thank you!
Hi Lon Hause,
Thanks for sharing. Glad this video resonated with you.
⚡Power On!⚡ / Avi
★ Get Your Questions Answered LIVE on TalkPowerBI Fridays www.learnpowerbi.com/talkpowerbi ★
Great overview!
Amazing! Thank you Avi. Great tutorial.
Hi Avi Great Stuff, I too keep it with relationships most of the time, I works naturally well !
I have seen few Power Bi tutorials but this one is most useful for me.
Great overview! How can you provide a numerical grouping. I want to group AR by days past invoice date or due date. I would imagine this would entail defining a measure, but that's as far as i can get.
Thanks for explaining the same feature in multiple ways.
I enjoyed my first power bi lesson, that's awesome!!
Hi Abeer Qassim.
Great to hear that, Abeer! Keep up the passion in learning Power BI!
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: czcams.com/video/AGrl-H87pRU/video.html ★
★ Subscribe and Join me LIVE for Talk Power BI Fridays czcams.com/users/PowerBIProlive ★
I'm a Tableau guy, and starting out in PBI and this is awesome, and so easy
Hi dmatthieu2.
Glad that this video has helped you out in using Power BI. If you get stuck, do join me LIVE every Friday - am Pacific on my #TalkPowerBI show.
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: czcams.com/video/AGrl-H87pRU/video.html ★
★ Subscribe and Join me LIVE for Talk Power BI Fridays czcams.com/users/PowerBIProlive ★
Great tips Avi. I really like your PowerBI energy!
Hi Benoît F. Larouche,
I really appreciate that, thank you!
⚡Power On!⚡ / Avi
★ Get Your Questions Answered LIVE on TalkPowerBI Fridays www.learnpowerbi.com/talkpowerbi ★
Thanks for the tutorial！I just got stuck by how to do summarization base on years, good tricks！
Thanks Avi. This is what I'm working on at work. The difference matters.
Perfect! All the best
this is amazing! thank you sir! great difference
You're welcome!
Thank you you save my day
Hi Avi, i want to know if grouping is the proper way or should i use some other function for my scenario, i have numerical data in 1 column and every number represents a different meaning.. like 4 = Serviced and 8,9,10 means cancel so what should i do..please guide me ?
i am going on learning power i with you! thank you, thank you and thank you!
Hi Avi.. I follow your CZcams Channel a lot and love the stuff you show... I was trying to Do a group by and add a column to my existing table. In the query editor it did not show any syntax error and I know for that there is no syntax error. However, After Applying it; it gave an error. Can you please throw a light on this behaviour?
Thank you!
Mandar
I would like to know how what in the relationship makes those tables accessable. I am having trouble understanding how cardinalities effect data visuals.
Great examples and video editing.
Hi D Mor.
Thanks
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: czcams.com/video/AGrl-H87pRU/video.html ★
★ Subscribe and Join me LIVE for Talk Power BI Fridays czcams.com/users/PowerBIProlive ★
haha, your email is really well written about excel , shout out to that
Hey! I'm unable to load anything in the query editor due to a DataSource.Error. Any suggestions? It's saying it could not find a part of the path.
Hello..Thanks for this awesome tutorial. I am using Group by-Count Rows to find how many times a date has appeared in the row. Can we do it via DAX? Please help..Thanks in Advance..
Amazing.!
I would use both DAX and Magic together when I want to see both static and responsive data together for comparison...
Hi Avi, how do you do a look up based on multiple colums in DAX
Great tips to learn though.
Hi junaid bashir.
Thanks for the positive response.
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: czcams.com/video/AGrl-H87pRU/video.html ★
★ Subscribe and Join me LIVE for Talk Power BI Fridays czcams.com/users/PowerBIProlive ★
Thank you, Avi!
Currently, am working on power bi report server and I couldn't retrieve the latest value based on the date. Would you suggest to me some tip, please?
Could you please explain in breif..?
iam unable to open the files(downloaded) in query editor.can any one tell me about this issue
thanks for recording these videos...
Thanks so much for your kind words. This really means a lot to me. Power On! / Team Avi
do you have course on Custom data connectors for power BI?
power BI mai right side mai Groupby likha hua hai...can we use that directly instead of right clicking the reference??
nice explanation
THANSK
Hi walid Khlil,
You're welcome.
⚡Power On!⚡ / Avi
★ Get Your Questions Answered LIVE on TalkPowerBI Fridays www.learnpowerbi.com/talkpowerbi ★
Thank you avi
Life Saver
Thank you. Am glad you found this useful. if you have any questions you can get them answered in our LIVE TalkPowerBI Q&A show.
👉 www.TalkPowerBI.com Or Join our LearnPowerBI Private Community
👉 web.learnpowerbi.com/waitlist-invite/
^RM
Great😊
I have an measure that is an if function based on a specified count. I would like to group those counts based on that measure. I.e if count is 1 then m, if count is 2 then m+1 else m+3. So I would like to know how many of m, m+1... are there
So i downloaded the Power Bi but I am getting error that source file is missing. So is there different download link to get source excel files ?
I have 8 QTR data in data table with QTR 3Q18 to 2Q20 ...and also with value column I want when I select for example 2Q20 in slicer then it should be shown last 5 QTR data on line graph ....what approach should I follow ?
Anyone can help by using PowerBI Dax ?
But I want to do group by with filter, how this we can achieve, means for every one month what is the last 3 month sales, where the filter will be last 3 month and sum of sales
Good Morning!
Could you help me with any tips on how to make a query that shows me which customers did not buy on a certain date with a certain product and that shows these customers on a map with location.
Ex: Would it be a fVendas Bank?
A customer bank
A Calendar Table
great video!
Thanks so much for your kind words. This really means a lot to me. Power On! / Team Avi
Hi Avi, I have to select max value , per day, per object and select all rows how to do that
how can i use ABS function in edit query?
How do I create a sub groups? I grouped the categories for example: infrastructure, oil etc by the company code. Now I want to break it down one component let’s say, oil by location so it shows Oil-Houston and oil- non-Hou but I want it shown all under the same column where it’s already grouped by the company code. Sorry it sounds complicated.
Please post video on user based restrictions to different reports within same app or workspace
May I know how to consolidate multiple data sheets.?
Hi, i have three tables and all the tables have relationship between them . how can i group by fields from different tables .. using DAX .
summarize('table1'[columnA], 'table2'[columnB], 'table3'[columnC], "charges", [my charge measure])
Why when I open the file and then click on transform data (edit queries) doesnt work.. What do I have to do so I am able to use the file
Hi may I know the screen recording software that you use?
Hey Sriram,
I generally use Snagit Studio. Microsoft MVPs get it for free :-)
Power On! . Avi
「あなたのコンテンツはとても感動的です」、
Thank you for your kind words.
If you have any additional questions or need further help you can also join our live events:
👉 www.TalkPowerBI.com
--Or--
👉 Consider joining our LearnPowerBI Training Program www.learnpowerbi.com/training
Power On! / Avi
Avi is Great.
Hi Nelson Tanyi, Thanks so much for being a subscriber🙏🏻
and thanks for the compliment.
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: czcams.com/video/AGrl-H87pRU/video.html ★
★ Get Your Questions Answered LIVE on TalkPowerBI Fridays www.learnpowerbi.com/talk ★
I have a question. I have data uploaded to Power BI. For example, I have 30 providers and each has a paid amount. How can I the following in a slicer: Top 1-10
Top 11-20
Top 21-30
Please let me know.
Hi Moayad Felimban.
I would add a Column in Query Editor with their Rank and another Column to show the rank as 1-10, 10-20 etc.
That's one way to do it. There might be others. If you're still stuck ask me in my next TalkPowerBI. I go live each Friday. Link below.
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: czcams.com/video/AGrl-H87pRU/video.html ★
★ Subscribe and Join me LIVE for Talk Power BI Fridays czcams.com/users/PowerBIProlive ★
Hi Moayad,
You can do either way by using Query editor (conditional Column),Dax if Statments...
I don't have the Reference option when I right-click on the Budget table. My version of Power BI desktop is Version: 2.84.981.0 64-bit (August 2020)
Figured out my error
How would you create categories for a column with more than 300-400 records? How would you find the few 3-4 records in the big pool to create a single group? Please help.
Hey Ayushi, you should create a mapping in Excel and load it into Power BI.
If you have any additional questions or need further help you can also join our live events:
👉 www.TalkPowerBI.com
Power On! / Avi
Can you tell me how to use PRODUCT & PRODUCTX DAX?
Hi Varun, PRODUCT and PRODUCTX are logically similar to SUM & SUMX (of course with the difference being one multiplies the values, whereas the other one adds them). Having said that I have not myself used PRODUCT/PRODUCTX. I understand they are used for calculating compoud interest and other such scenarios.
If you are stuck with a specific problem, best I can recommend is, pre-submit your question and join me in my next LIVE TalkPowerBI Q&A show and maybe I can help you there.
👉 www.learnpowerbi.com/talkpowerbi#qa
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: czcams.com/video/AGrl-H87pRU/video.html ★
★ Get Your Questions Answered LIVE on TalkPowerBI Fridays www.learnpowerbi.com/talkpowerbi ★
I have a list of years in my power bi queries which i imported from excel. The only column in the table is years which are
1988, 1990, 1993, 1997, 2002, 2008, 2013, 2018
But in my graph it sorts these years as
2008, 2013, 2002, 2018, 1988, 1990, 1993, 1997.
This is when the x-axis type is categorical
When it's continuous the values get 1985, 1990, 1995, 2000, 2005, 2010, 2015, 2020.
One way it sorts wrong and the other way its values get wrong
What can i do to fix this?
Thanks in advance to whoever answers
Create another Column based on Years using Switch then click on year column & modelling->sort by new column created or you can directly use sort by Year in Power PIvot
You do a really good job of explaining the topic and your energy is great.
The download file doesn't work at this time. Can you fix it or give me new download link.
Hi Charlie Holland,
Sorry to hear you are having trouble with the download files. Please reach out to us via email at avi @ avising.com so we can assist you further.
⚡Power On!⚡ / Avi
★ Get Your Questions Answered LIVE on TalkPowerBI Fridays www.learnpowerbi.com/talk ★
Lets assume that we have two components of salary i.e. Basic and Conveyance an employee receives PKR100 and 200 respectively for these two components in the month of April and the same employee receives PKR200 and 100 respectively for the same components in the month of May. Can Power BI DAX give me the information in another table under column Basic and Conveyance as 100 and -100 for the same employee? Please note that we have more than 14,000 employees and every month I have to identify the same by looking for the variance in payroll and identifying the staff who receive different payments as compared to last month. Thank you for your help!
if there are more information required I can share the sample file through email.
What is the difference between using the group bys this way versus just creating the 4 different table views on your canvas and selecting the group by column and column you want to sum. Seems like powerBI does this naturally.
Yup that's the third approach I showed. And you are right it is the best one. But it's good to know the other ones as you may need them in some cases.
Any help? Could not load data in Query Editor - > DataSource.NotFound: Microsoft Access: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 64-bit version of the Access Database Engine 2010 Access Database Engine OLEDB provider may be required to read 'AdventureWorks.accdb'. To download the client software, visit the following site: go.microsoft.com/fwlink/?LinkID=285987.
Sorry, cant watch this one. You didn't wear your yellow bow tie....
Ha! I know...it's not quite the same without the Bow Tie :-)
By the way, if you were ever curious Why I started wearing Bow-Tie and what it represents, check out one of my older videos:
czcams.com/video/QwDy1cOVQEs/video.html
See you around :-)
Oh...if you are interested, LearnPowerBI Enrollment is opening soon. If you haven't joined already, this would be a great time.
And if you have any additional questions you can get them answered in our LIVE TalkPowerBI Q&A show.
👉 web.learnpowerbi.com/waitlist/
⚡Power On!⚡ / Avi
★ Power BI Complete Tutorial: czcams.com/video/AGrl-H87pRU/video.html ★
★ Get Your Questions Answered LIVE on TalkPowerBI Fridays www.learnpowerbi.com/talk ★
Use this in a model with 30 Gigs and you will see that "PRO" you do not have anything my friend. I´m sorry .
your video is good but your face camera destruct my concentration.
Thank you for your feedback. That's a good one :-)
Very useful