{"id":418,"date":"2018-07-13T08:41:22","date_gmt":"2018-07-13T06:41:22","guid":{"rendered":"http:\/\/cwiok.pl\/?p=418"},"modified":"2018-07-13T08:41:22","modified_gmt":"2018-07-13T06:41:22","slug":"accumulating-lists-in-power-query-using-list-acculumate","status":"publish","type":"post","link":"https:\/\/cwiok.pl\/index.php\/en\/2018\/07\/13\/accumulating-lists-in-power-query-using-list-acculumate\/","title":{"rendered":"Accumulating lists in Power Query using… List.Acculumate()"},"content":{"rendered":"

List.Accumulate is a very powerful function that is often overlooked, when doing Power Query transformations. Its basic behaviour is explained in the reference<\/a>. How this function works is accumulates a result from a specified operation (accumulator function)<\/strong> starting from the initial value – seed –\u00a0 and going row by row till the end of a specified list. Radacad<\/a> greatly explained the possibilities of the function. I treat List.Accumulate as a function that lets me access a previous row. Something that is very often expected by customers using Excel sheets as data sources. This enables me to e.g. detect a change in ID or date \u2013 from my experience 90% of the cases.<\/p>\n

\"\"<\/a><\/p>\n

But how do to it? Examples provided by Radacad are great, but I want to truely accumulate lists in Power Query\u2026<\/p>\n

Let us go through the syntax basing on an easy example of cummulative sum. In this case I have a simple list with 10 numbers.<\/p>\n

\"\"<\/a><\/p>\n

To create a cummulative sum using List.Accumulate, I type:<\/p>\n

Cumulative_new  = List.Accumulate(#\"Changed Type\"[Values],{0}, (sum,index) =>  if index = #\"Changed Type\"[Values]{0} then {#\"Changed Type\"[Values]{0}} else sum& {List.Last(sum) + index})<\/pre>\n
The result is a list with cummulative values:<\/p>\n
\"\"<\/a><\/p>\n
But first let us go through the syntax:<\/p>\n
List.Accumulate(#\"Changed Type\"[Values],{0}, (sum,index) =>  if index = #\"Changed Type\"[Values]{0} then {#\"Changed Type\"[Values]{0}} else sum& {List.Last(sum) + index})\r\n\r\n<\/pre>\n
First part points to the list that we will iterate through. In our case it is a single column with numbers 1 to 10:<\/p>\n
#\"Changed Type\"[Values]<\/pre>\n
Next part is our seed \u2013 initial value of the accumulator. In our case {0} – a list with a single value 0:<\/p>\n
{0}<\/pre>\n
And finally the accumulator:<\/p>\n
(sum,index) =>  if index = #\"Changed Type\"[Values]{0} then {index} else sum& {List.Last(sum) + index})<\/pre>\n
For a reference: index \u2013 this is the current value of the row in the list you are iterating through, sum \u2013 this is the result of the accumulator.<\/p>\n
If the current value of the list is the first value of the list, the result of the accumulator is a list with the first value \u2013 which is index, thus writing it as {index}. Below line is important, because we do not want the seed to be the first value.We cannot set the seed to the first value, because accumulator will try to add seed to the first value.<\/p>\n
if index = #\"Changed Type\"[Values]{0} then {index}<\/pre>\n
Code below states: If index is not the first value of the iterated list, I want to append the list (remember {}) containing one value \u2013 List.Last(sum) + index to the result of the accumulator (in the second step, this would be {1}). List.Last(sum) + index means that I want to take the last value of the accumulator and add index to it. This will produce a list with one value e.g.{3} which I will append to the list \u2013 resulting in {1,3}. After repeating steps 10 times I will end up with a list of cumulative sums.<\/p>\n
else sum& {List.Last(sum) + index})<\/pre>\n
The final step is combining the table with the list:<\/p>\n
#\"Converted to Table\" = Table.FromList(Cumulative_new, Splitter.SplitByNothing(), null, null, ExtraValues.Error),    \r\n\/\/I merge two columns into a table\r\nAdd_columns= Table.FromColumns(Table.ToColumns( #\"Changed Type\")&{Cumulative_new})<\/pre>\n
\"\"<\/a><\/p>\n
My whole code:<\/p>\n
let\r\n     Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(\"i45WMlSK1YlWMgKTxmDSBEyagkkzMGkOJi3ApCWYNDRQio0FAA==\", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Values = _t]),\r\n     #\"Changed Type\" = Table.TransformColumnTypes(Source,{{\"Values\", Int64.Type}}),\r\n      Cumulative_new  = List.Accumulate(#\"Changed Type\"[Values],{0}, (sum,index) =>  if index = 1 then {index} else sum& {List.Last(sum) + index}),\r\n      #\"Converted to Table\" = Table.FromList(Cumulative_new, Splitter.SplitByNothing(), null, null, ExtraValues.Error),\r\n  Cumulative_new_new  = List.Accumulate(#\"Changed Type\"[Values], {0}, (sum,index) =>   sum&{ index}),\r\n     \r\n     \/\/I merge two columns into a table\r\n     Add_columns= Table.FromColumns(Table.ToColumns( #\"Changed Type\")&{Cumulative_new})\r\n   \r\nin\r\n     Add_columns<\/pre>\n
Another example<\/a> is straight out of the PowerBI forum. The case is simple, create a special index that grows whenever the column with the product changes:<\/p>\n
\"\"<\/a><\/p>\n
The whole code for the solution:<\/p>\n
let\r\n     Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(\"i45WclSK1UElPTFIRxxsuEgsAA==\", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t]),\r\n     #\"Changed Type\" = Table.TransformColumnTypes(Source,{{\"Category\", type text}}),\r\n     #\"Added Index\" = Table.AddIndexColumn(#\"Changed Type\", \"Index\", 0, 1),\r\n    \r\n     New_app = List.Accumulate(#\"Added Index\"[Index],{0}, (state,current) =>  if current = 0 then {1} else if #\"Added Index\"{current-1}[Category] = #\"Added Index\"{current}[Category] then state & {List.Last(state)} else state & {List.Last(state)+1}),\r\n     Add_columns= Table.FromColumns(Table.ToColumns(#\"Added Index\")&{New_app}),\r\n    \r\n     #\"Removed Columns1\" = Table.RemoveColumns(Add_columns,{\"Column2\"}),\r\n     #\"Renamed Columns\" = Table.RenameColumns(#\"Removed Columns1\",{{\"Column1\", \"Product\"}, {\"Column3\", \"Index\"}})\r\nin\r\n     #\"Renamed Columns\"<\/pre>\n
The line worth mentioning is:<\/p>\n
New_app = List.Accumulate(#\"Added Index\"[Index],{0}, (state,current) =>  if current = 0 then {1} else if #\"Added Index\"{current-1}[Category] = #\"Added Index\"{current}[Category] then state & {List.Last(state)} else state & {List.Last(state)+1}),<\/pre>\n
It is very similar to the previous example. Only exception is if else condition which evaluates expression:<\/p>\n
#\"Added Index\"{current-1}[Category] = #\"Added Index\"{current}[Category]<\/pre>\n
Mind that we iterate through index column. This line checks if Category in the current row is the same as in the previous one. If it is, it appends the list with the last value:<\/p>\n
state & {List.Last(state)}<\/pre>\n
If it isn\u2019t, adds 1 to the last value:<\/p>\n
state & {List.Last(state)+1}),<\/pre>\n
Here<\/a> is my yet another example from the forum.<\/p>\n
And that is it! Let me know, what you think if this approach to List.Accumulate()!<\/p>\n
Thanks!<\/p>\n","protected":false},"excerpt":{"rendered":"
List.Accumulate is a very powerful function that is often overlooked, when doing Power Query transformations. Its basic behaviour is explained in the reference<\/a>. How this function works is accumulates a result from a specified operation (accumulator function)<\/strong> starting from the initial value – seed –  and going row by row till the end of a specified list. Radacad<\/a> greatly explained the possibilities of the function. I treat List.Accumulate as a function that lets me access a previous row. Something that is very often expected by customers using Excel sheets as data sources. This enables me to e.g. detect a change in ID or date \u2013 from my experience 90% of the cases.<\/p>\n
\"\"<\/a><\/p>\n
Read More<\/a><\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[41],"tags":[],"class_list":["post-418","post","type-post","status-publish","format-standard","hentry","category-power-query"],"_links":{"self":[{"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/posts\/418","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/comments?post=418"}],"version-history":[{"count":1,"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/posts\/418\/revisions"}],"predecessor-version":[{"id":1305,"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/posts\/418\/revisions\/1305"}],"wp:attachment":[{"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/media?parent=418"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/categories?post=418"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/tags?post=418"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}