Tabela przestawna nie pokazuje nowych danych po odświeżeniu zapytania

Power Query w Excelu

Tabela przestawna nie pokazuje nowych danych po odświeżeniu zapytania

W skrócie

Tabela przestawna oparta na wyniku Power Query to podstawa wielu raportów w Excelu. Kłopot pojawia się, gdy po kliknięciu Odśwież wszystko dane w tabeli zapytania są już nowe, a tabela przestawna dalej pokazuje stare liczby. Sprawdziliśmy to zachowanie w Excelu i pokazujemy, skąd się bierze, które ustawienie je usuwa i jak upewnić się, że raport pokazuje aktualne dane.

Jak to wygląda w praktyce

Do folderu źródłowego doszedł nowy plik, a Ty klikasz na karcie Dane przycisk Odśwież wszystko. Tabela z wynikiem zapytania ma nowe wiersze, ale tabela przestawna zbudowana na tej tabeli pokazuje te same sumy co przed odświeżeniem. Excel nie zgłasza błędu. Po drugim kliknięciu Odśwież wszystko tabela przestawna nagle pokazuje nowe dane.

W naszym teście w Excelu tabela przestawna sumowała kolumnę z wyniku zapytania i pokazywała 105. Do źródła dopisaliśmy wiersz z wartością 1000. Po pierwszym Odśwież wszystko suma w tabeli zapytania wynosiła 1105, a tabela przestawna wciąż pokazywała 105. Dopiero drugie odświeżenie dało 1105.

Dlaczego tak się dzieje

Tabela przestawna nie czyta tabeli źródłowej na bieżąco. Przy odświeżeniu kopiuje dane, które w tej chwili stoją w tabeli w arkuszu, i liczy z tej kopii. Zapytanie z zaznaczonym polem Włącz odświeżanie w tle działa w tle. Microsoft opisuje, że takie odświeżanie oddaje Ci kontrolę nad Excelem, zamiast kazać czekać na koniec. Jeśli tabela przestawna odświeży się, zanim zapytanie wpisze nowe wiersze, pobierze stare dane. Przy drugim kliknięciu zapytanie nie wnosi już nic nowego, a tabela przestawna czyta gotowy wynik.

W naszym teście po odznaczeniu pola Włącz odświeżanie w tle wystarczało jedno Odśwież wszystko: suma w tabeli zapytania i w tabeli przestawnej była taka sama. W zapytaniu z kursu, utworzonym zwykłym importem, to pole było zaznaczone.

Dwie inne przyczyny dają podobny objaw. Przycisk Odśwież (Alt+F5) odświeża tylko zaznaczoną tabelę, więc tabela przestawna zostaje ze starymi danymi. Zapytanie z wyczyszczonym polem Odśwież to połączenie podczas odświeżania wszystkiego jest pomijane przez Odśwież wszystko.

Jak to rozwiązać krok po kroku

1. Otwórz właściwości zapytania. Na karcie Dane kliknij Zapytania i połączenia, w okienku kliknij prawym przyciskiem zapytanie, którego wynik zasila tabelę przestawną, i wybierz Właściwości.
2. Wyłącz odświeżanie w tle. Na karcie Użycie, w sekcji Sterowanie odświeżaniem, odznacz Włącz odświeżanie w tle i kliknij OK. Od tej chwili Excel czeka, aż zapytanie skończy pracę, zanim przejdzie dalej.
3. Sprawdź udział w Odśwież wszystko. W tym samym oknie pole Odśwież to połączenie podczas odświeżania wszystkiego musi być zaznaczone. Bez niego Odśwież wszystko pominie to zapytanie i tabela zapytania też zostanie stara.
4. Odświeżaj całość, nie jedną tabelę. Używaj Odśwież wszystko na karcie Dane albo skrótu Ctrl+Alt+F5. Przycisk Odśwież i skrót Alt+F5 odświeżają tylko zaznaczony obiekt.
5. Albo zbuduj tabelę przestawną wprost na zapytaniu. Kliknij zapytanie w okienku prawym przyciskiem, wybierz Załaduj do i zaznacz Raport w formie tabeli przestawnej. Taka tabela przestawna bierze dane z zapytania bez pośredniej tabeli w arkuszu.
6. Przy dużych danych użyj modelu danych. W oknie Importowanie danych zaznacz Dodaj te dane do modelu danych i zbuduj tabelę przestawną na modelu. Model przyjmuje miliony wierszy, a Microsoft zaznacza, że zapytania pobierającego dane do modelu danych nie da się uruchomić w tle.

Jak sprawdzić, że zadziałało

Po zmianie powtórz nasz test na swoim pliku. Zapamiętaj sumę końcową tabeli przestawnej, dopisz do źródła jeden wiersz z łatwą do rozpoznania wartością albo dodaj kolejny plik do folderu, jak w lekcji o ładowaniu danych, i kliknij Odśwież wszystko tylko raz. Suma końcowa tabeli przestawnej ma się zgadzać z sumą tej samej kolumny w tabeli zapytania.

Jeśli plik odświeża się przy otwieraniu albo co kilka minut, zostaw odświeżanie w tle wyłączone we wszystkich zapytaniach, które zasilają tabele przestawne i wykresy przestawne. Ustawienie dotyczy każdego odświeżenia tego zapytania, nie tylko ręcznego.

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