Hi PQ Guru
I had tried using function to involve to get running totals for Fields1, and was wondering whether can you group by field get Index for each field 1 value, then groupby again to insert Running total in (see the following code in attachment), but get error
For all advise, please
let
Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Field 1", type text}, {"QTY", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Field 1", Order.Ascending}, {"QTY", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Field 1"}, {{"MyData", each _, type table [Field 1=nullable text, QTY=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([MyData],"Index",1,1,Int64.Type)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"MyData"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"QTY", "Index"}, {"QTY", "Index"}),
#"Grouped Rows1" = Table.Group(#"Expanded Custom", {"Field 1"}, {{"Grouped", each _, type table [Field 1=nullable text, QTY=number, Index=number]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom1", each Table.AddColumn([Grouped],"RTotal",List.Sum(List.Range(#"Grouped Rows1"[QTY],0,[Index]))))
in
#"Added Custom1"
Hi Chris,
That query is throwing an error because you don't have a Column named QTY but I think that may be beside the point.
You've only got 2 columns in your source so once you have grouped by Field 1 you haven't got any other columns to group by.
What is the result you are hoping to get? Can you please provide an example.
Regards
Phil
Hi Philip,
I used function produce the following output
| Field 1 | QTY | GrpByRunningTotal |
| D001 | 2 | 2 |
| D001 | 4 | 6 |
| D001 | 89 | 95 |
| D002 | 4 | 4 |
| D002 | 54 | 58 |
| T003 | 6 | 6 |
| T003 | 11 | 17 |
| T003 | 67 | 84 |
| Z001 | 61 | 61 |
| Z002 | 12 | 12 |
| Z003 | 6 | 6 |
| Z004 | 2 | 2 |
| Z004 | 5 | 7 |
| Z004 | 6 | 13 |
| Z005 | 4 | 4 |
Hi Chris,
Yes I saw that in the workbook so if that is what you want as a result, I don't understand what you are asking for help with as you've already done it?
Regards
Phil
Hi Philip,
I am just exploring without using function method is it possible, that is the last custom column if I can insert the running total into 2nd grouping (with Index inserted in the first grouping), so I am seeking advise from you guys whether can be done, or definitely need to use function
Thank you !
Hi Chris,
I'd use a custom function to create a running total.
But that aside, your question was actually can you group by field get Index for each field 1 value, then groupby again to insert Running total.
If you've grouped Field 1 then what column are you going to group by again? An Index column will just have numbers 1,2,3 etc so can't be grouped. And with Field 1 grouped you can't insert a running total column, it'll just be a grand total for each group in Field 1.
If I am missing something then you need to supply an example of what you want as a result. The table in post #3 above does not show any groupings.
Phil
Hi Philip
Thanks for your response and explanation
