Summarizing Data with Group By in Power Query

Рет қаралды 21,646

Күн бұрын

★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Check out the blog post & download the example file★
exceloffthegrid.com/power-que...
★ About this video ★
In Excel, there are many ways to group and summarize data, and in Power Query Group By performs this task similarly to Excel functions, but with a slightly different purpose. In Excel, we generally summarize to display a result; however, in Power Query, we summarize to reduce data to the optimal granularity level. The Power Query Group By transformation is simple yet powerful. So, let's take a look at how we can use it.
0:00 Introduction
0:52 Group By views: basic and advanced
2:06 Summarize Data: Example #1
2:56 Summarize Data: Example #2
4:08 Summarize Text
6:02 All Rows option
6:55 Conclusion
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel #PowerQuery

Пікірлер: 31

@vwij1736 5 ай бұрын

Great video Mark.

@IvanCortinas_ES Жыл бұрын

The last case of "All rows" is very interesting. Excellent and brilliant explanation Mark. Thanks!!

@ExcelOffTheGrid Жыл бұрын

Because it creates a Table in each row. We can then use all the M code Table.xxxxx functions in a Custom Column to manipulate those Tables. It's mind blowing.

@leftindependent 2 ай бұрын

Excellent job! Thank you 😊

@ExcelOffTheGrid 2 ай бұрын

You’re welcome.

@c.e.bingham2079 10 ай бұрын

Well done! Excellent presentation, as I learned something new. Thanks!

@ExcelOffTheGrid 10 ай бұрын

Glad you enjoyed it!

@Pradonis7 Ай бұрын

Your content is excellent. You are very didactic. Thank you!!

@ExcelOffTheGrid Ай бұрын

Thank you, I appreciate that! 😁

@user-dl6vn9jj8s Жыл бұрын

Thanks. I find this really useful.

@andrewsinha2785 Жыл бұрын

Clear, perfect Thanks

@ExcelOffTheGrid Жыл бұрын

You're welcome!

@SeemaSharma-lj3cf 9 ай бұрын

Thank you for sharing

@ExcelOffTheGrid 9 ай бұрын

My pleasure

@ohdjrp4 Жыл бұрын

Thank you!

@ExcelOffTheGrid Жыл бұрын

You're welcome!

@Picla_Peremohy Жыл бұрын

Another excellent video Mark. I was previously aware of the Text.Combine trick but was plagued by the duplicate values in the cell. You made my day today with the List.Distinct() addition. Since the need for summarizing text is quite common, do you think Microsoft will add that as an option instead of forcing us to use the Sum workaround as you present?

@ExcelOffTheGrid Жыл бұрын

You're welcome Jim. It would make sense to add Text.Combine to that list (along with as many other possible options), but I think it's unlikely for quite a while. I know the Power Query team are working on some pretty big stuff at the moment, so I doubt that will revert any resources for something like this at the moment.

@orkunakar9057 5 ай бұрын

how can I apply Group by where I have multıple colums? product X Y Y sum for each months seperately ?

@dougmphilly 10 ай бұрын

list distinct --- i needed this bad

@ExcelOffTheGrid 10 ай бұрын

I’m glad you found it useful.

@Charly_MC Жыл бұрын

Excellent. I have a question: is possible Create groups for specific dates ? Example: my field date is day by day but I need group by month (Sum values) and March I need day by day 😅 thanks

@ExcelOffTheGrid Жыл бұрын

Add a month column, then perform the Group By. You can always remove the column later if you don’t need it any more.

@leiterservices 11 ай бұрын

Using the Text.combine, the resulting column still shows errors - how do you fix that?

@ExcelOffTheGrid 11 ай бұрын

Is the column a text data type?

@eslamfahmy87 Жыл бұрын

Hello sir...if my data have Duplicated on one column and I need to make a group by by this column to be unique data for my file

@ExcelOffTheGrid Жыл бұрын

Select the column then click Home > Remove Rows > Remove Duplicates

@eslamfahmy87 Жыл бұрын

@@ExcelOffTheGrid thank you 😊 for fast response, highly appreciated but I know this feature, and as I understood from ur reply that's my need not coverd on group by tool. Am right ?

@ExcelOffTheGrid Жыл бұрын

So you want to Group By and then remove duplicates inside each Group? To do that - use Group By with the operation on All Rows. Then add a custom column with the Table.Distinct function applied to each row. Finally, expand the Tables.

@eslamfahmy87 Жыл бұрын

@@ExcelOffTheGrid thnx a lot 😊 I will try it ...

@eslamfahmy87 Жыл бұрын

@@ExcelOffTheGrid actually, I tried but it's doesn't work good as when I expand the custom table all Duplicated are reflected again despite of I used table.distinct function!!