Za dużo elementów w wyliczeniu po przestawieniu kolumny - błędy w komórkach

Power Query w Excelu, Power BI i Fabric

Za dużo elementów w wyliczeniu po przestawieniu kolumny - błędy w komórkach

W skrócie

Komunikat „Za dużo elementów w wyliczeniu” pojawia się, gdy przestawiasz kolumnę poleceniem Kolumna przestawna, a w opcjach zaawansowanych wybierasz Nie agreguj. Zapytanie działa, ale w części komórek nowej macierzy zamiast wartości stoi Error. Pokazujemy, jak znaleźć zdublowane pary, jak dobrać agregację i co zrobić, gdy każda wartość ma zostać widoczna osobno.

Jak to wygląda w praktyce

Krok Kolumna przestawna się wykonuje i tabela ma tyle wierszy, ile się spodziewasz, ale niektóre komórki pokazują Error. Pozostałe komórki tego samego wiersza mają poprawne wartości. Kliknięcie w puste miejsce obok napisu Error pokazuje komunikat:

Expression.Error: Za dużo elementów w wyliczeniu, aby można było ukończyć operację.

Angielski oryginał to Expression.Error: There were too many elements in the enumeration to complete the operation. Szczegóły tego błędu to lista wartości, które trafiły do jednej komórki. W naszym teście sklep Nordvella Wrocław miał dwa wiersze za styczeń, ze sprzedażą 100 i 50, i właśnie te dwie wartości zawierała lista, a komórka za luty pokazała poprawne 80.

Komunikat łatwo pomylić z „Jest za mało elementów w wyliczeniu”. Oba mówią o liczbie elementów, ale tu problemem jest nadmiar wartości, a nie ich brak.

Dlaczego tak się dzieje

Kolumna przestawna zamienia wartości jednej kolumny, na przykład Miesiąc, w nagłówki nowych kolumn, a wartości z kolumny wskazanej w polu Kolumna wartości wpisuje do komórek. Każda komórka wyniku to para: wiersz, u nas sklep, i nowa kolumna, u nas miesiąc. Z agregacją, na przykład Suma, Power Query łączy wszystkie wartości pary w jedną liczbę. Z opcją Nie agreguj zakłada, że wartość jest dokładnie jedna. Gdy jest ich więcej, zgłasza błąd, ale tylko w tej komórce.

Zdublowane pary mają dwa źródła:

• dane nie są jeszcze zagregowane, na przykład przestawiasz linie paragonów zamiast sumy sprzedaży na sklep i miesiąc,
• w danych są powtórzone wiersze. Sprawdziliśmy w Excelu, że dwa jednakowe wiersze ze sprzedażą 100 dla tej samej pary też dają błąd, dopóki nie usuniesz duplikatów.

Wiersz wyniku wyznaczają wszystkie kolumny tabeli poza przestawianą i kolumną wartości. Dodatkowa kolumna, na przykład numer powtórzenia, rozbija wynik na więcej wierszy, a jej brak skleja wartości, które powinny trafić do osobnych wierszy.

Jak to rozwiązać krok po kroku

1. Znajdź zdublowane pary. Kliknij prawym przyciskiem zapytanie w panelu Zapytania i wybierz Duplikuj. W kopii kliknij prawym przyciskiem krok Kolumna przestawna i wybierz Usuwaj do końca. Potem na karcie Strona główna kliknij Grupowanie według, w trybie Zaawansowane grupuj po Sklep i Miesiąc z operacją Zlicz wiersze i przefiltruj wynik na liczby większe niż 1.
2. Jeśli macierz ma pokazywać sumy albo liczby, usuń w oryginalnym zapytaniu krok Kolumna przestawna krzyżykiem i przestaw kolumnę jeszcze raz. Zaznacz kolumnę Miesiąc, na karcie Przekształć kliknij Kolumna przestawna, wskaż Kolumna wartości, rozwiń Opcje zaawansowane i w liście Agreguj funkcję wartości wybierz Suma, a do zliczania Liczność (wszystkie). W naszym teście Suma dała 150, a zliczanie 2.
3. Gdy duplikaty są przypadkowe, usuń je przed przestawieniem: zaznacz wszystkie kolumny, na karcie Strona główna rozwiń Usuń wiersze i wybierz Usuń duplikaty. Znikną tylko wiersze identyczne w całości. Różne wartości dla tej samej pary nadal wymagają agregacji.
4. Jeśli każda wartość ma zostać osobno, ponumeruj powtórzenia w grupie. Usuń krok Kolumna przestawna, a potem pogrupuj dane po Sklep i Miesiąc z operacją Wszystkie wiersze, a w pasku formuły zastąp formułę kroku wersją = Table.Group(Źródło, {"Sklep", "Miesiąc"}, {{"Wiersze", each Table.AddIndexColumn(_, "Nr", 1, 1), type table}}), zostawiając nazwę poprzedniego kroku z Twojej formuły. Rozwiń kolumnę Wiersze ikoną w nagłówku z kolumnami Sprzedaż i Nr, a dopiero potem przestaw Miesiąc z opcją Nie agreguj. W naszym teście zamiast jednego wiersza z błędem powstały dwa: ze 100 i z 50 za styczeń.
5. Przy przestawianiu tekstów, na przykład uwag do sklepu, lista Agreguj funkcję wartości nie ma funkcji łączącej teksty. Dopisz ją w pasku formuły jako ostatni argument Table.Pivot: each Text.Combine(_, ", "). W naszym teście dwie uwagi za styczeń dały jeden tekst: remont, inwentaryzacja.
6. Jeśli zdublowana para oznacza błąd w danych, na przykład dwa wpisy budżetu dla tego samego sklepu i miesiąca, popraw źródło. Agregacja ukryłaby problem w sumie, a numeracja powieliłaby go w wyniku.

Jak sprawdzić, że zadziałało

Kliknij ostatni krok: w macierzy nie ma napisów Error, a po włączeniu na karcie Widok pola Jakość kolumn każda kolumna miesiąca pokazuje 0% przy słowie Błąd. Przy agregacji Suma porównaj sumę jednej kolumny miesiąca z sumą tego miesiąca przed przestawieniem: muszą być równe. Zdublowane pary znalezione w pierwszym kroku powinny teraz mieć w macierzy sumę wszystkich swoich wartości.

Nawrotom zapobiegniesz, przestawiając kolumnę na końcu przepisu, na danych już zgrupowanych do jednego wiersza na parę, i wybierając Nie agreguj tylko wtedy, gdy masz pewność, że para się nie powtórzy. Kopię zapytania z grupowaniem możesz zostawić jako kontrolę: dopóki nie ma zdublowanych par, zwraca pustą tabelę.

Wróć do listy: 88 najczęstszych pytań i problemów związanych z Power Query

Szkolenie Microsoft Excel - Power Query --> Dwa dni warsztatów z Excela: pobieranie danych z plików, folderów, SharePointa i baz SQL, ich czyszczenie i łączenie w Power Query, praca w języku M, a na koniec model danych i oparta na nim tabela przestawna. Prowadzi Sebastian Stasiak.

To szkolenie może być dofinansowane dla Ciebie z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5