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!
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!
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.
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
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
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?
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
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.
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?
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)
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?
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?
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
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.
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?
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
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
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… 🤔
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.
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..
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.
Awesome, thank you just what I needed for my report
Sweet.. thanks a lot! I am glad it helped.
Grazie.
Thanks for the tip!
@@GoodlyChandeep a humble acknowledgment for the amazing content of your channel. Thank you
Thanks
Thanks for the Tip Mariusz!
The best channel and teacher for power query thanks Chandeep
Glad you think so!
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!
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!
Exactly.😊
Salute to your dedications and appreciate your knowledge.. Thanks for making such nice tutorials..
Pleasure!
Thank you so much for this automated record and it worked perfectly fine with my datasets
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.
Glad you enjoyed it!
This guy is absolutely amazing. Great video as always.
Thanks !
Thank you very much for such great solution and time consuming process...... Appreciate
Glad you liked it!
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.
Oh and thanks for the downloads.
Good and clear explanation. i've subcribed to learn more. thank you
Great, thank you!
Simply amazing!!!!!! Thank you for share
thanks!, it’s really helpful !
Hi Goodly, great video! Is it possible to create a blank row after each total?
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
Great video and very usefull as always. Good job Chandeep!
Happy to help!
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
Stunning, thanks for sharing this
Glad you enjoyed it!
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?
It's Ridiculous. Amazing!
Thanks Orlean!
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
which camera do u use?
Thanks for tutorial
thanks very much to Sample file
I can follow your Video
formidable, exactly what I need... is it possible to add a blank row between two subtotals ? Huge thank you
Clever and beautiful 👍
Thank you Kebin! 😊
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.
Beautiful 👏🏼👏🏼👏🏼
Thank you! Cheers!
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?
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)
of course! amazing work with a lot of information.
Glad you think so!
Lovely..... Thanks for sharing
Thank you! Cheers!
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?
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.
Slick!
Excellent. Thank you!
Glad you enjoyed it!
Great work thank you so much
Glad you enjoyed it!
Hi Chandeep! in list.accumulate can we proced like this : if Value.Type(source[c]) = number then list.Sum(...) else "Total"
Thanks and looking for the text file of M Code.
goodly.co.in/subtotal-and-column-total-in-power-query/
From Sri Lanka 🇱🇰🇱🇰🇱🇰
Thanks!
Superb
It is great, Thank you
Glad you like it!
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?
Awesome !
Wow... Belleza
Thanks!
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
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.
What about if I want to show total on the top?
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 :)
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?
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
Trying to reach you on email since long. Can you let me know when is your new batch starting?
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
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… 🤔
The answer is in your code,sorry I saw it at last
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.
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..
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.
kindly share the file
goodly.co.in/subtotal-and-column-total-in-power-query/
👍