Need Assistance - M Code adding two Custom Columns with relevant calculations

[JustCraig]

Good day,

Trust you're all well. Totally new to M.

I require some assistance in creating a step in Power Query that creates two custom columns which rely on values from the current and previous rows respectively. Essentially the intention is as follows, as one would easily do in Excel (C and D):

	A	B	C	D
1	x	y	0	(C1+A1)-B1
2	x	y	D1	(C2+A2)-B2
3	x	y	D2	(C3+A3)-B3

Some Sample Data:

SiteName	DateSubmitted	Received_Bins	Sorted_Bins	Index
MEN	2024/06/19 22:54	80	80	1
MEN	2024/06/20 17:27	97	90	2
MEN	2024/06/20 22:23	65	60	3
MEN	2024/06/21 10:23	70	70	4
MEN	2024/06/21 17:00	103	110	5
MEN	2024/06/22 11:52	75	70	6
MEN	2024/06/22 16:09	106	100	7
MEN	2024/06/23 15:56	97	97	8
MEN	2024/06/23 22:34	65	70	9

Intended Outcome: 2 additional Columns added 'Opening' and 'Closing'

SiteName	DateSubmitted	Received_Bins	Sorted_Bins	Index	Opening	Closing
MEN	2024/06/19 22:54	80	80	1	0	0
MEN	2024/06/20 17:27	97	90	2	0	7
MEN	2024/06/20 22:23	65	60	3	7	12
MEN	2024/06/21 10:23	70	70	4	12	12
MEN	2024/06/21 17:00	103	110	5	12	5
MEN	2024/06/22 11:52	75	70	6	5	10
MEN	2024/06/22 16:09	106	100	7	10	16
MEN	2024/06/23 15:56	97	97	8	16	16
MEN	2024/06/23 22:34	65	70	9	16	11

Reason it needs to happen in Power Query is due to the fact that the table needs to be unpivoted in the last step prior to being applied. 

Any assistance would be greatly appreciated

[dufoq3 · Accepted solution]

Hi @JustCraig, check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY8xDsMwCEWvgjxHCmADNXvH9gJR7n+Ngq1kqJzlDZ+nj/5xlM/7W7bCyG1H3akDs0uL6IUT5/YvMQKZs0XUBx6kaOIakUpiKREQTslwYi2ZY94JUyVaagxELpw18tgVkjr20aWDS60CiYveC20txcLaroX58PwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SiteName = _t, DateSubmitted = _t, Received_Bins = _t, Sorted_Bins = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"SiteName", type text}, {"DateSubmitted", type datetime}, {"Received_Bins", type number}, {"Sorted_Bins", type number}}),
    Buffered = List.Buffer(Table.ToRows(Table.SelectColumns(ChangedType,{"Received_Bins", "Sorted_Bins"}))),
    GeneratedOpeningClosing = Table.FromRecords(List.Generate(
        ()=> [ x = 0, Opening = 0, Closing = Opening + Buffered{x}{0} - Buffered{x}{1} ],
        each [x] < List.Count(Buffered),
        each [ x = [x]+1, Opening = [Closing], Closing = Opening + Buffered{x}{0} - Buffered{x}{1} ],
        each [[Opening], [Closing]]
)),
    ChangedType2 = Table.TransformColumnTypes(GeneratedOpeningClosing,{{"Opening", type number}, {"Closing", type number}}),
    StepBack = ChangedType,
    Combined = Table.FromColumns(Table.ToColumns(StepBack) & Table.ToColumns(ChangedType2), Value.Type(StepBack & ChangedType2))
in
    Combined

[Greg_Deckler]

@JustCraig 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY8xDsQgDAS/gqgjxTbYBPcp7z4Q8f9vBDviipPTjNBqtHivK3/Ob94yAdUdZMeeiJTrjA5YwDy2f5EgYVNqM+oOEykWZyOVGQkbTCyRiAnhERss1BexKfhlYDqivTlSKSEqk9Xx6pQXURS6d4rTb4jUkpCV5bfccMTiXF7qWu6f9zzGDQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SiteName = _t, DateSubmitted = _t, Received_Bins = _t, Sorted_Bins = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SiteName", type text}, {"DateSubmitted", type datetime}, {"Received_Bins", Int64.Type}, {"Sorted_Bins", Int64.Type}, {"Index", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Opening", each let
  Index = [Index],
  MyTable = Table.SelectRows( #"Changed Type", each [Index] < Index),
  Received = List.Sum( Table.SelectColumns( MyTable,"Received_Bins")[Received_Bins]),
  Sorted = List.Sum( Table.SelectColumns( MyTable, "Sorted_Bins")[Sorted_Bins]),
  Result = Received - Sorted + 0
in
  Result),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Closing", each let
  Index = [Index],
  MyTable = Table.SelectRows( #"Added Custom1", each [Index] <= Index),
  Received = List.Sum( Table.SelectColumns( MyTable,"Received_Bins")[Received_Bins]),
  Sorted = List.Sum( Table.SelectColumns( MyTable, "Sorted_Bins")[Sorted_Bins]),
  Result = Received - Sorted
in
  Result)
in
    #"Added Custom"

[JustCraig]

Thank you for your time and solution @Greg_Deckler, very much appreciated. I had to change the Index to run per Group (SiteName) which suited your proposed solution but the Opening and Closing calcs were off for some reason. I'll try it again in due course

[dufoq3 · Accepted solution]

Hi @JustCraig, check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY8xDsMwCEWvgjxHCmADNXvH9gJR7n+Ngq1kqJzlDZ+nj/5xlM/7W7bCyG1H3akDs0uL6IUT5/YvMQKZs0XUBx6kaOIakUpiKREQTslwYi2ZY94JUyVaagxELpw18tgVkjr20aWDS60CiYveC20txcLaroX58PwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SiteName = _t, DateSubmitted = _t, Received_Bins = _t, Sorted_Bins = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"SiteName", type text}, {"DateSubmitted", type datetime}, {"Received_Bins", type number}, {"Sorted_Bins", type number}}),
    Buffered = List.Buffer(Table.ToRows(Table.SelectColumns(ChangedType,{"Received_Bins", "Sorted_Bins"}))),
    GeneratedOpeningClosing = Table.FromRecords(List.Generate(
        ()=> [ x = 0, Opening = 0, Closing = Opening + Buffered{x}{0} - Buffered{x}{1} ],
        each [x] < List.Count(Buffered),
        each [ x = [x]+1, Opening = [Closing], Closing = Opening + Buffered{x}{0} - Buffered{x}{1} ],
        each [[Opening], [Closing]]
)),
    ChangedType2 = Table.TransformColumnTypes(GeneratedOpeningClosing,{{"Opening", type number}, {"Closing", type number}}),
    StepBack = ChangedType,
    Combined = Table.FromColumns(Table.ToColumns(StepBack) & Table.ToColumns(ChangedType2), Value.Type(StepBack & ChangedType2))
in
    Combined

[JustCraig]

Thank you @dufoq3 

It works perfectly! Very much appreciated.

What I've subsequently had to do now on the table is group it by SiteName and reapply the Index. So every new Site would start the Index over. 

How can one incorporate an 'each index < [index]' within the List.Generate so that it starts over each time the index does?

[dufoq3 · Accepted solution]

Hi, with my solution you doesn't need [Index] column. Try this - this will calculate Opening and Closing for each SiteName:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY8xDsMwCEWvgjxHCmADNXvH9gJR7n+Ngq1kqJzlDZ+nj/5xlM/7W7bCyG1H3akDs0uL6IUT5/YvMQKZs0XUBx6kaOIakUpiKREQTslwYi2ZY94JUyVaagxELpw18tgVkjr20aWDS60CiYveC20txcLaroX58PwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SiteName = _t, DateSubmitted = _t, Received_Bins = _t, Sorted_Bins = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"SiteName", type text}, {"DateSubmitted", type datetime}, {"Received_Bins", type number}, {"Sorted_Bins", type number}}),
    GroupedRows = Table.Group(ChangedType, {"SiteName"}, {{"FnTransform", each 
        [ Buffered = List.Buffer(Table.ToRows(Table.SelectColumns(_,{"Received_Bins", "Sorted_Bins"}))),
          GeneratedOpeningClosing = Table.FromRecords(List.Generate(
                ()=> [ x = 0, Opening = 0, Closing = Opening + Buffered{x}{0} - Buffered{x}{1} ],
                each [x] < List.Count(Buffered),
                each [ x = [x]+1, Opening = [Closing], Closing = Opening + Buffered{x}{0} - Buffered{x}{1} ],
                each [[Opening], [Closing]]
        )),
          ChangedType2 = Table.TransformColumnTypes(GeneratedOpeningClosing,{{"Opening", type number}, {"Closing", type number}}),
          StepBack = _,
          Combined = Table.FromColumns(Table.ToColumns(StepBack) & Table.ToColumns(ChangedType2), Value.Type(StepBack & ChangedType2))
        ][Combined]
    , type table}}),
    Combined = Table.Combine(GroupedRows[FnTransform])
    
in
    Combined

[JustCraig]

Thank you, works perfectly! Much appreciated again

[dufoq3]

You're welcome 😉