I want to calculate a Delta Weeks column in Power Query WeekNum[current row] - WeekNum[previous row]
I found a way to do it using the [Index] column, but it is painfully slow, and my table is 100k rows.
Also, after this, I need another column who would count the distinct values from the beginning up to that row.
Most of the weeks are consecutive, so basically the distinct count will increase when they are not.
(I don't know how to do this in Power Query).
I found a way to do it using the [Index] column, but it is painfully slow, and my table is 100k rows.
Code:
[COLOR=#000000][FONT=inherit]let
[/FONT][/COLOR][COLOR=#660066][FONT=inherit] Source[/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#660066][FONT=inherit]Excel[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#660066][FONT=inherit]CurrentWorkbook[/FONT][/COLOR][COLOR=#666600][FONT=inherit](){[[/FONT][/COLOR][COLOR=#660066][FONT=inherit]Name[/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#008800][FONT=inherit]"Table3"[/FONT][/COLOR][COLOR=#666600][FONT=inherit]]}[[/FONT][/COLOR][COLOR=#660066][FONT=inherit]Content[/FONT][/COLOR][COLOR=#666600][FONT=inherit]],[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
[/FONT][/COLOR][COLOR=#880000][FONT=inherit]#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Product", type text}, {"WeekNum", Int64.Type}}),[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
[/FONT][/COLOR][COLOR=#880000][FONT=inherit]#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
[/FONT][/COLOR][COLOR=#880000][FONT=inherit]#"Added Custom" = Table.AddColumn(#"Added Index", "Delta Weeks", each try Source[WeekNum]{[Index]} - Source[WeekNum]{[Index]-1} otherwise 0)[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
[/FONT][/COLOR][COLOR=#000088][FONT=inherit]in
[/FONT][/COLOR][COLOR=#880000][FONT='inherit']#"Added Custom"[/FONT][/COLOR]
Also, after this, I need another column who would count the distinct values from the beginning up to that row.
Most of the weeks are consecutive, so basically the distinct count will increase when they are not.
(I don't know how to do this in Power Query).
Last edited: