Napotkano odwołanie cykliczne podczas obliczania - zapytania odwołują się do siebie

Power Query w Excelu, Power BI i Fabric

Napotkano odwołanie cykliczne podczas obliczania - zapytania odwołują się do siebie

W skrócie

Napotkano odwołanie cykliczne w Power Query to błąd, który pojawia się na przykład po zmianach w działającym skoroszycie: ktoś dołożył scalenie, podmienił źródło zapytania albo dodał kolumnę z funkcją niestandardową. Pokazujemy, skąd bierze się pętla między zapytaniami, jak znaleźć ją w oknie Zależności zapytań i jak przebudować zapytania, żeby wynik został ten sam, a pętla zniknęła.

Jak to wygląda w praktyce

Zapytanie zatrzymuje się na kroku, a w podglądzie zamiast danych widzisz żółty pasek z komunikatem Expression.Error: Napotkano odwołanie cykliczne podczas obliczania. (ang. A cyclic reference was encountered during evaluation.). Pole szczegółów jest puste, więc komunikat nie mówi, które zapytania tworzą pętlę. Trzeba je znaleźć samodzielnie.

Przykład na danych z kursu: zapytanie Sprzedaz_miesieczna scala sprzedaż z katalogiem tProdukty, żeby dociągnąć nazwy i kategorie produktów. Ktoś chce zobaczyć w katalogu łączną sprzedaż każdego produktu, więc w zapytaniu tProdukty dodaje krok Scal zapytania ze Sprzedaz_miesieczna. Od tej chwili katalog potrzebuje sprzedaży, a sprzedaż katalogu. Taki układ dwóch tabel scalanych nawzajem odtworzyliśmy w Excelu i dał dokładnie ten komunikat.

Dlaczego tak się dzieje

Power Query liczy tylko to, czego potrzebuje wynik: zaczyna od kroku wskazanego po in i dociąga kroki oraz zapytania, z których ten krok korzysta. Gdy po drodze trafia na wartość, którą właśnie liczy, nie ma od czego zacząć i zgłasza odwołanie cykliczne. Ten sam mechanizm działa w jednym wyrażeniu: let a = b, b = a in a oraz rekord [a = b, b = a] kończą się identycznym komunikatem.

Pętla powstaje między innymi w trzech sytuacjach:

• scalenie albo dołączenie w obie strony, jak w przykładzie z katalogiem,
• nowe źródło zapytania bazowego wskazujące zapytanie, które powstało z niego przez Odwołanie: odwołanie zaczyna się od wyniku oryginału, więc oryginał nie może z niego korzystać,
• funkcja niestandardowa, która czyta zapytanie, w którym ją wywołujesz: zapytanie korzysta z funkcji, a funkcja z zapytania.

Dwie rzeczy, które sprawdziliśmy, żeby nie szukać w złym miejscu. Krok odwołujący się do własnej nazwy nie tworzy pętli: silnik szuka tej nazwy poza krokiem i zgłasza nieznaną nazwę. Funkcja, która z tabeli, w której ją wywołano, czyta tylko liczbę wierszy, zadziałała bez błędu, więc ostatni z trzech układów nie zawsze kończy się komunikatem.

Jak to rozwiązać krok po kroku

1. Otwórz mapę zapytań: w edytorze Power Query na karcie Widok kliknij Zależności zapytań. Okno pokazuje źródła, zapytania pomocnicze i końcowe oraz strzałki między nimi.
2. Kliknij zapytanie z błędem. Podświetlą się zapytania, które z niego korzystają. Jeśli wśród nich jest zapytanie, z którego ono samo korzysta w kroku Źródło, w scalaniu albo w dołączaniu, masz pętlę. Przy dłuższym łańcuchu sprawdź tak kolejno każde zapytanie na ścieżce.
3. Potwierdź to w kodzie. Otwórz Edytor zaawansowany podejrzanego zapytania i poszukaj nazw innych zapytań, na przykład Źródło = Sprzedaz_miesieczna albo trzeciego argumentu Table.NestedJoin, czyli tabeli dołączanej przy scalaniu. Każda taka nazwa to strzałka na mapie.
4. Ustal role. Zapytania bazowe, tu tProdukty i Sprzedaz_miesieczna, pobierają i czyszczą dane, a zapytania końcowe je łączą. Bazowe nie mogą korzystać z końcowych. Usuń z zapytania bazowego krok, który sięga do zapytania końcowego, krzyżykiem przy kroku na liście Zastosowane kroki.
5. Zbuduj łączenie jako nowe zapytanie. W przykładzie z katalogiem kliknij prawym przyciskiem Sprzedaz_miesieczna, wybierz Odwołanie i w nowym zapytaniu użyj polecenia Grupowanie według po kolumnie Kod produktu z sumą kolumny Ilość. Potem zaznacz tProdukty i na karcie Strona główna rozwiń strzałkę przy Scal zapytania i wybierz Scal zapytania jako nowe, łącząc katalog z pogrupowaną sprzedażą po kolumnie Kod produktu. Oba zapytania źródłowe zostają bez zmian.
6. Gdy pętlę tworzy funkcja niestandardowa, przenieś dane, które funkcja czyta, do osobnego zapytania bazowego i niech z niego korzystają zarówno funkcja, jak i tabela, w której ją wywołujesz. W naszym teście taki układ zwrócił poprawne wyniki bez błędu.
7. Kliknij Odśwież podgląd i sprawdź po kolei każde zapytanie, które wcześniej pokazywało błąd.

Jak sprawdzić, że zadziałało

Otwórz ponownie Zależności zapytań. Strzałki mają biec w jedną stronę, od źródeł przez zapytania bazowe do końcowych, i żadna ścieżka nie może wrócić do zapytania, z którego wyszła. Potem w Excelu na karcie Dane kliknij Odśwież wszystko i porównaj liczbę wierszy wyników końcowych z tą sprzed przeróbki.

Żeby pętla nie wróciła, trzymaj prosty podział: źródło i czyszczenie w zapytaniach bazowych, a scalanie, grupowanie i zestawienia w zapytaniach końcowych tworzonych przez Odwołanie albo Scal zapytania jako nowe. Zanim zmienisz źródło istniejącego zapytania, kliknij je w oknie Zależności zapytań i sprawdź, co z niego korzysta.

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