POWER QEURY move from cumulaive to line aomunt

[nirrobi]

Hi,

I have table with cumulative data - 2 M rows

what is the best way to add line amount column that reflect the line amount (ExpectedReslt in the table below)

attach sample data with expected result

Category	Referance	Cumulative	ExpectedReslt
A	1	2	2
A	2	4	2
A	3	8	4
B	1	1	1
B	2	2	1
B	3	3	1
C	1	10	10

[Ashish_Mathur]

Hi,

It is quite somple to solve this with a calculated column formula using DAX.  Would you be interested?

[nirrobi]

Yes please

please be notet that my relevant table is 2,000,000 rows so the formula should be super effective:-)

[Ashish_Mathur]

Hi,

This calculated column formula works

=if(ISBLANK(CALCULATE(max(Data[Referance]),FILTER(Data,Data[Category]=EARLIER(Data[Category])&&Data[Referance]<EARLIER(Data[Referance])))),Data[Cumulative],LOOKUPVALUE(Data[Cumulative],Data[Category],Data[Category],Data[Referance],CALCULATE(max(Data[Referance]),FILTER(Data,Data[Category]=EARLIER(Data[Category])&&Data[Referance]<EARLIER(Data[Referance])))))

Hope this helps.

[Zubair_Muhammad]

@nirrobi 

Here is a way of doing it in Power Query.

See file attached as well.

Basically I created another custom column as Reference -1 and then merged it with current Reference Column to get previous cumulative

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYiMwjtWBiIB4JigixkBsARYFiThBdUEwTMQIihEixlAMEXGG6TCAELGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t, Referance = _t, Cumulative = _t, ExpectedReslt = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Referance", Int64.Type}, {"Cumulative", Int64.Type}, {"ExpectedReslt", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Referance]-1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Category", "Custom"}, #"Added Custom", {"Category", "Referance"}, "Added Custom", JoinKind.LeftOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Cumulative"}, {"Added Custom.Cumulative"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Added Custom", "DesiredResult", each if [Added Custom.Cumulative]= null then [Cumulative] else[Cumulative]-[Added Custom.Cumulative]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Added Custom.Cumulative"})
in
    #"Removed Columns"

[nirrobi]

great !!

many thanks

I will check of 2 M rows table and let you know

I tried somethong similar (but diff) but the engine couldn't complete the calculation

10x