Forum

Index column base f...
 
Notifications
Clear all

Index column base for column values & Running Total

6 Posts
3 Users
0 Reactions
226 Views
(@bluesky63)
Posts: 162
Estimable Member
Topic starter
 

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
 
Posted : 18/12/2019 9:20 pm
(@bluesky63)
Posts: 162
Estimable Member
Topic starter
 

similar to the case in this thread when they use DAX calculate

https://community.powerbi.com/t5/Desktop/Cumulative-sum-by-date-by-condition/td-p/561053

just wanted to explore any easier way (for biz user) to use PQ to achieve the same

 

Thank you !

 
Posted : 18/12/2019 11:15 pm
(@mynda)
Posts: 4761
Member Admin
 

Hi Chris,

Looks like this post has your answer.

Mynda

 
Posted : 19/12/2019 8:17 am
(@bluesky63)
Posts: 162
Estimable Member
Topic starter
 

Hi Mynda,

I saw this blog this morning and it seems quite complicated and thought you all got another alternative   🙂

 
Posted : 19/12/2019 9:19 am
(@bluesky63)
Posts: 162
Estimable Member
Topic starter
 

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

 
Posted : 19/12/2019 10:16 am
(@catalinb)
Posts: 1937
Member Admin
 

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.

 
Posted : 19/12/2019 11:22 am
Share: