Kumulowanie list przy użyciu… List.Accumulate()
List.Accumulate to potężna funkcja, która jest często pomijana podczas robienia transformacji w edytorze Power Query. Jej podstawowa funkcjonalność jest wytłumaczona w dokumentacji. Działa poprzez kumulowanie wyników zadanej operacji (accumulator function) zaczynąjąc od wartości źródłowej i przechodząc wiersz po wierszu aż do końca wybranej listy.Radacad świetnie wytłumaczył podstawowe możliwości tej funkcji. Ja traktuję List.Accumulate jako funkcję, która pozwala mi dostać się do wartości z poprzedniego wiersza. Jest to częsta transformacja oczekiwana przez klientów, którzy korzystają z Excela jako źródła danych. 90% przypadków to sprawdzenie czy ID lub data ma inną wartość niż w poprzednim wierszu.
Ale jak to zrobić? Przykłady Radaca’a są super, ale chciałbym kumulować listy!
Zapoznajmy się ze składną na podstawie prostego przykładu skumulowanej sumy. W tym wypadku mam prostą tabelę z liczbami od 1 do 10.
Aby stworzyć listę ze skumulowaną sumą przy użyciu List.Accumulate(), wpisuję:
|
1 |
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}) |
Wynikiem jest lista ze skumulowanymi wartościami:
Przejdźmy przez składnię:
|
1 |
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}) |
Pierwszy argument wskazuje na listę, przez którą będziemy iterować. W naszym wypadku jest to jedna kolumna:
|
1 |
#"Changed Type"[Values] |
Drugim argumentem jest wartość źródłowa – seed. W naszym wypadku {0} – czyli lista mająca jeden element 0:
|
1 |
{0} |
Trzecim argumentem jest operacja – accumulator function:
|
1 |
(sum,index) => if index = #"Changed Type"[Values]{0} then {index} else sum& {List.Last(sum) + index}) |
Dla wyjaśnienia: index – tutaj oznacza wartość w wierszu w jakim “znajduje się” funkcja, sum – to wynik operacji. Te nazwy można dowolnie modyfikować.
Jeśli index jest równa pierwszej wartości z listy, accumulator przyjmie wartość {index} – czyli lista z index. Poniższa linijka jest bardzo ważna, ponieważ nie chcemy wartości źródłowej jak pierwszej. Nie możemy też zmienić seed’a na pierwszą wartość w liście, ponieważ accumulator będzie chciał dodać seed to tej pierwszej wartości, dublując ją.
|
1 |
if index = #"Changed Type"[Values]{0} then {index} |
Poniższy kod oznacza: Jeśli index nie jest pierwszą wartością listy, chciałbym dołączyć do wyniku accumulatora (w drugim kroku będzie to {1}), listę, która ma wartość List.Last(sum) + index. List.Last(sum) + index oznacza, że wezmę ostatni element wyniku accumulatora i dodam do niego index. To zwróci mi listę z jednym elementem np. {3}, którą dodam do wyniku otrzymując {1,3}. Po powtórzeniu kroków 10 razy otrzymam skumulowane sumy.
|
1 |
else sum& {List.Last(sum) + index}) |
Ostatnim krokiem jest połączenie tabeli i stworzonej listy:
|
1 2 3 |
#"Converted to Table" = Table.FromList(Cumulative_new, Splitter.SplitByNothing(), null, null, ExtraValues.Error), //I merge two columns into a table Add_columns= Table.FromColumns(Table.ToColumns( #"Changed Type")&{Cumulative_new}) |
Cały kod:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
let 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]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Values", Int64.Type}}), Cumulative_new = List.Accumulate(#"Changed Type"[Values],{0}, (sum,index) => if index = 1 then {index} else sum& {List.Last(sum) + index}), #"Converted to Table" = Table.FromList(Cumulative_new, Splitter.SplitByNothing(), null, null, ExtraValues.Error), Cumulative_new_new = List.Accumulate(#"Changed Type"[Values], {0}, (sum,index) => sum&{ index}), //I merge two columns into a table Add_columns= Table.FromColumns(Table.ToColumns( #"Changed Type")&{Cumulative_new}) in Add_columns |
Inny przykład jest prosto z forum Power BI. Zadanie jest proste – stworzyć specjalny indeks, który wzrasta gdy wartość w kolumnie Product się zmienia:
Kod mojego rozwiązania:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
let 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]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), 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}), Add_columns= Table.FromColumns(Table.ToColumns(#"Added Index")&{New_app}), #"Removed Columns1" = Table.RemoveColumns(Add_columns,{"Column2"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Column1", "Product"}, {"Column3", "Index"}}) in #"Renamed Columns" |
Warto zwrócić uwagę na linię:
|
1 |
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}), |
Jest bardzo podobna do poprzedniego przykładu. Jedyna zmiana to warunek “if else”, który sprawdza wyrażenie:
|
1 |
#"Added Index"{current-1}[Category] = #"Added Index"{current}[Category] |
W tym wypadku iterujemy przez kolumnę z indeksem. Powyższa linia sprawdza czy wartości w kolumnie Category w tym i poprzednim wierszu są takie same. Jeśli tak, to accumulator dołącza do listy jej ostatni element:
|
1 |
state & {List.Last(state)} |
W przeciwym wypadku dodaje 1 do ostatniej wartości:
|
1 |
state & {List.Last(state)+1}), |
Tutaj mój jeszcze jeden przykład z forum.
I to wszystko! Dajcie znać co myślicie o tym podejściu do List.Accumulate()!
Dzięki!
