Log In
Register
Home
August 21, 2019
Offline
Hi,
Is it possible in Power Query UI (if not custom column) to achieve Index column base on column field value (e.g.), I would like to achieve the running total base on index of field value (D001) using List.Sum("#Add Index"[QTY], [Index]))
or you got any other alternative method to achieve running total (see Desire result in attached sample)
Thank you
Chris
| Field | QTY | Index |
| D001 | 2 | 1 |
| D001 | 4 | 2 |
| D001 | 89 | 3 |
| D002 | 54 | 1 |
| D002 | 4 | 2 |
| T003 | 6 | 1 |
| T003 | 11 | 2 |
August 21, 2019
Offline
similar to the case in this thread when they use DAX calculate
https://community.powerbi.com/.....d-p/561053
just wanted to explore any easier way (for biz user) to use PQ to achieve the same
Thank you !
August 21, 2019
Offline
Hi Mynda,
I saw this blog this morning and it seems quite complicated and thought you all got another alternative 🙂
August 21, 2019
Offline
Hi Mynda,
Created function ( not sure whether is correct)
(Input as table) =>
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}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
cumulativ_total = Table.AddColumn(#"Added Index", "Total", each List.Sum(List.Range(#"Added Index"[QTY],0,[Index]))),
#"Removed Columns" = Table.RemoveColumns(cumulativ_total,{"Index"}),
extract_total = cumulativ_total[Total]
in
extract_total
I was stuck after Use new function in grouping in the post
Need your help to complete the rest and probably explain to me briefly explain to me the grouping function and the remaining code....
Appreciated and thanks in advance
Chris
November 8, 2013
Offline
There is always an alternative, you can build your own logic and steps.
Considering the first 2 column of the sample data you provided, you can use this query to add a grouped running Total:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Field", type text}, {"QTY", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Field"}, {{"All", each _, type table}}),
AddIndexToAllTablesInColumn=Table.TransformColumns(#"Grouped Rows",{"All", (tbl)=> Table.AddIndexColumn(tbl,"Index",1,1)}),
AddRunningTotalToAllTablesInColumn=Table.TransformColumns(AddIndexToAllTablesInColumn,{"All", (tbl)=> Table.AddColumn(tbl,"RT", (t2)=> List.Sum(List.Range(tbl[QTY],0,t2[Index])))}),
#"Expanded All" = Table.ExpandTableColumn(AddRunningTotalToAllTablesInColumn, "All", {"QTY", "RT"}, {"QTY", "RT"})
in
#"Expanded All"
Grouping the initial table and returning All Rows will give you the "All" column, where each row has all the qty for that group only.
I used 2 other steps to Transform the All column: first I added an index column to each table in that column, then I transformed again the same "All" column to create the grouped running total based on the index column of each table.
There can be lots of other variations, using List.Acumulate, and SelectRows.
1 Guest(s)
