Subtotal and Column Total in Power Query

[@GeorgeKlucsarits]

Hi Chandeep! Once again I'm faced with an obscure challenge and I find that you have a video solving my exact problem. Not only is my challenge solved, but I learned a lot more about Power Query once again. Thanks for posting such great content!

[@GeorgeKlucsarits]

Good day, Chandeep. I've been trying to download the M-code file from your site but so far no luck. I've entered my email and clicked download but nothing happens. I'm stuck on a step for creating the subtotals based on grouping and I'm hoping that a detailed review of your solution will get me over that hurdle. Many thanks in advance!

[@shri_420]

Exactly.😊

[@javedkhan-tz6fn]

The best channel and teacher for power query thanks Chandeep

[@GoodlyChandeep]

Glad you think so!

[@KuldeepSingh-nq1vi]

Salute to your dedications and appreciate your knowledge.. Thanks for making such nice tutorials..

[@GoodlyChandeep]

Pleasure!

[@christophelombart]

Thanks for this video ! This is a great example to start with Power Query. Is it possible to format differently the Total Row like put it in bold, change the color, .... ? Thanks

[@SamanthaMayne-d5g]

Hello! Thank you for this awesome video :) Re: The part you mentioned about "skipping columns" -- When I get the row total it is including the product# into the sum which I don't want. How do I ask it to skip that for the row Totals?

[@acarolinoa]

Hello, It was amazing :) I would like to ask, how about if I wanted to add a row wit HIGH and Low as a subtitle and a blank row between the subtotals so they could be more visible? thank you

[@kinleyzam620]

Thank you so much for this automated record and it worked perfectly fine with my datasets

[@Dindralia]

Hi Goodly, great video! Is it possible to create a blank row after each total?

[@Dindralia]

For the people that want a blank row to show below the subtotals, make sure to replace from "RowTotal": RowTotal = Table.InsertRows( InputTable, Table.RowCount(InputTable), {TotalRecord} ), BlankRow = List.Accumulate( Table.ColumnNames(RowTotal), [], (s,c)=> Record.AddField(s, c, null) ), RowTotalWithBlank = Table.InsertRows( RowTotal, Table.RowCount(RowTotal), {BlankRow} ), ColTotal = Table.AddColumn( RowTotalWithBlank, "Col Total", each List.Sum(List.Select(Record.ToList(_), each _ is number)) ) in if AddColumnTotal = true then ColTotal else RowTotalWithBlank

[@joserochefort7778]

formidable, exactly what I need... is it possible to add a blank row between two subtotals ? Huge thank you

[@imronnesia5170]

Good and clear explanation. i've subcribed to learn more. thank you

[@jvh7103]

Magic, thanks Chandeep. Question if i have a other table, say i only want column 10 and 11 to (sub)total. Is there a way to just point out to those columns instead of using InitialColumn?

[@roywilson9580]

Thanks for the interesting video. Not sure that I would ever want to add a totals or subtotals row in Power Query but still very interesting.

[@GoodlyChandeep]

Glad you enjoyed it!

[@williamarthur4801]

I think you should do a video on the function you shared, I'm pretty creatin how it works, but as to skipping columns, but it would be interesting to see explained properly.

[@syedaneesdurez7197]

Thank you very much for such great solution and time consuming process...... Appreciate

[@GoodlyChandeep]

Glad you liked it!

[@odallamico]

Simply amazing!!!!!! Thank you for share

[@radekminarik7417]

This guy is absolutely amazing. Great video as always.

[@GoodlyChandeep]

Thanks !

[@patrickharilantoraherinjat2994]

Hi Chandeep! in list.accumulate can we proced like this : if Value.Type(source[c]) = number then list.Sum(...) else "Total"

[@akrimnialhabil4541]

thanks!, it’s really helpful !

[@williamarthur4801]

Really great video, learned a lot and will be going over a few times, ok with the manual except I used Record from list . Re List .Accumulate, you did a video on non date running totals in DAX, I ( with help from on high) came up with this for M ; Listt.Skip( List.Accumulate( Source [Units] , {0}, (A,B)=> A & List.Last(A) + B } ) , 1 ) Then wrap this so; Table.FromColumns( Table.ToColumns(Source) & {Accumulate List} Table.ColumnNames(Source) & {"Running Total"} ), can be done all in one, might make a good challenge for most inventive way.

[@williamarthur4801]

Oh and thanks for the downloads.

[@harshaphilips3286]

Thanks for sharing the informative video. I was trying the same with the function -but it led to my query being stuck and not completing the load. Can you think of any possible reasons for the same and how to overcome this?

[@sumardjo]

Thanks for tutorial thanks very much to Sample file I can follow your Video

[@joserochefort7778]

Sorry and I have a question, at the end when you use the function, Fnrow&ColTotal(Source,2 this number 2 according what you say is to ignore columns Category and Product, so I dont understand why the label "Total" appears in the sixth row , for me it should not because the function should not meet the items of the two first columns that can't be added together (they are string)

[@user-mma173]

The way I solve this is by creating a single row table using Table.FromColumns with the columns I need to sum. Then, I append it to main table.

[@GoodlyChandeep]

Slick!

[@orleanCosta]

It's Ridiculous. Amazing!

[@GoodlyChandeep]

Thanks Orlean!

[@rhaps2008]

Awesome, thank you just what I needed for my report

[@GoodlyChandeep]

Sweet.. thanks a lot! I am glad it helped.

[@SachinShinde-vj1xj]

I am working on a model where I want to store values of certain parameter on every Monday of week and they should not change once the dashboard is refreshed. Can you help with the way to do it?

[@sanchitgoyalvlogs]

which camera do u use?

[@oscarmendez-roca9181]

Great video and very usefull as always. Good job Chandeep!

[@GoodlyChandeep]

Happy to help!

[@juliethramos7864]

Awesome! I'm running into an issue. I need to have Subtotals (per category) and one last row with the Grand Totals at the end. When I use the Fx, the Grand total is duplicating the amount that results after SUM all the subtotals...like duplicating amounts.

[@akrimnialhabil4541]

Awesome !

[@stefaanlouette]

Stunning, thanks for sharing this

[@GoodlyChandeep]

Glad you enjoyed it!

[@alexbarbucristi]

Superb

[@zahoorsarbandi2982]

of course! amazing work with a lot of information.

[@GoodlyChandeep]

Glad you think so!

[@narasimhaprasad847]

Lovely..... Thanks for sharing

[@GoodlyChandeep]

Thank you! Cheers!

[@mnowako]

Excellent. Thank you!

[@GoodlyChandeep]

Glad you enjoyed it!

[@goodyearspokane]

I just do a Reference, Group By and Append to get the total line and then alter the code to get the word Total in. I hate UI's and like to do it all in PQ. In SQL I would SELECT "---------------" to even get the separator lines above and below the total line

[@kebincui]

Clever and beautiful 👍

[@GoodlyChandeep]

Thank you Kebin! 😊

[@strangersingh6770]

Great work thank you so much

[@GoodlyChandeep]

Glad you enjoyed it!

[@mnowako]

Thanks

[@GoodlyChandeep]

Thanks for the Tip Mariusz!

[@Aviji2525]

Trying to reach you on email since long. Can you let me know when is your new batch starting?

[@GeertDelmulle]

Negative, Chandeep, meaning: not too technical at all. :-) This is nice, more integrated challenge. The only thing I’m thinking is: I wonder if it’s possible to do this without List.Accumulate… 🤔

[@AnbarasuAnnamalai]

It is great, Thank you

[@GoodlyChandeep]

Glad you like it!

[@syedaneesdurez7197]

Thanks and looking for the text file of M Code.

[@GoodlyChandeep]

goodly.co.in/subtotal-and-column-total-in-power-query/

[@toyjeep]

How are you? I wanted to ask you something, that is a little offtopic, but maybe you can help me. When I work in the Power Query formula bar, I can't see that list of options that appear when we start writing. For instance, in the minute 4.33 you start typing and all the references to Table appear. In my excel that doesn't happen, do you know how to activate it?

[@brianxyz]

Do the following in Power Query: File > Options and Settings > Query Options > Power Query Editor (under Global Options) > check the box on the right under Formula to enable M Intellisense in the formula bar

[@مسلم_سني_مسلم]

Beautiful 👏🏼👏🏼👏🏼

[@GoodlyChandeep]

Thank you! Cheers!

[@muzniahamed5887]

From Sri Lanka 🇱🇰🇱🇰🇱🇰

[@GoodlyChandeep]

Thanks!

[@alterchannel2501]

how could you do the subtotals without the function. I don't want to invoke the function. I would like to add sub totals to each table of the gruoped column and then combine then

[@GeorgeKlucsarits]

Hi Chandeep, I'm interested in this, too. I'm having difficulty downloading your solution and I'm getting stuck at creating the record after doing the grouping.

[@randolfojolongutierrez5311]

Wow... Belleza

[@GoodlyChandeep]

Thanks!

[@massimocolaceci]

Grazie.

[@GoodlyChandeep]

Thanks for the tip!

[@massimocolaceci]

@@GoodlyChandeep a humble acknowledgment for the amazing content of your channel. Thank you

[@silverfunnel6819]

👍

[@lamkwokyu]

I have a more crazy but not efficient solution: 0. Connect to the data source in the video 1. Create a new column called Total = All the value in each row is "Total" 2. Transform -> Group by the new column Total ->and aggregate the column we need to sum up ->then it would create a small table with one line which is the subtotal of the whole table -> remove the column Product: 3. Append the table and the newly created table..

[@saadspawn]

What about if I want to show total on the top?

[@daimpi]

at 12:26 when he uses Table.InsertRows , instead of using 5 you can use 0, that should give you the totals at the top :)

[@joserochefort7778]

The answer is in your code,sorry I saw it at last

[@ssmith8717]

Downloads from the internet are restricted by my company. I’ve watched the entire video and now unable to complete. It would be good to include the video with the required signup or login of each email address.

[@hasnainsabir4893]

kindly share the file

[@GoodlyChandeep]

goodly.co.in/subtotal-and-column-total-in-power-query/