Power Query nie widzi nowych wierszy dopisanych w arkuszu - zakres zamiast tabeli

Power Query w Excelu

Power Query nie widzi nowych wierszy dopisanych w arkuszu - zakres zamiast tabeli

W skrócie

Gdy Power Query nie widzi nowych wierszy dopisanych w arkuszu, przyczyny szukaj w źródle zapytania, a nie w samym odświeżaniu. Jeśli zapytanie czyta obszar o stałym adresie, na przykład nazwany zakres, zamiast tabeli Excela, która rośnie razem z danymi, nowe wiersze po prostu do niego nie należą. Pokazujemy, jak sprawdzić, z czego czyta zapytanie, jak przepiąć je na tabelę i co zrobić z plikami z innych systemów, w których zapisany rozmiar arkusza nie zgadza się z danymi. Wszystkie warianty sprawdziliśmy w polskim Excelu na pliku testowym.

Jak to wygląda w praktyce

Dopisujesz pod danymi kolejne wiersze, na przykład sprzedaż z nowego tygodnia, klikasz Odśwież wszystko i nic się nie zmienia: tabela wynikowa kończy się na tym samym wierszu, a sumy w tabeli przestawnej stoją w miejscu. Power Query nie zgłasza błędu, bo odczytał dokładnie ten obszar, który wskazuje źródło.

W naszym pliku testowym arkusz miał 5 wierszy sprzedaży o łącznej ilości 15, a nazwany zakres utworzony wcześniej obejmował komórki A1:B4. Zapytanie oparte na tej nazwie zwróciło 3 wiersze z sumą 6 i kończyło się na sklepie Nordvella Kraków Galeria. Tabela Excela z tymi samymi danymi zwróciła wszystkie 5 wierszy.

Dlaczego tak się dzieje

Zapytanie utworzone przyciskiem Z tabeli/zakresu czyta dane funkcją Excel.CurrentWorkbook. Jej opis mówi, że zwraca tabele, nazwane zakresy i tablice dynamiczne, ale nie arkusze. Opis przycisku dodaje, że zapytanie łączy się z zaznaczoną tabelą albo nazwanym zakresem, a zwykły zakres, który nie należy do żadnego z nich, zostaje zamieniony na tabelę. Nazwany zakres to stały adres, na przykład A1:B4, więc wiersze dopisane niżej do niego nie należą. Tabela Excela rozszerza się sama, gdy zaczniesz wpisywać w komórce tuż pod jej ostatnim wierszem albo wkleisz tam dane, zaczynając od skrajnej lewej kolumny.

Druga odmiana dotyczy plików zapisanych przez inne systemy. Arkusz w pliku xlsx zawiera zapisany rozmiar obszaru z danymi, a funkcja Excel.Workbook domyślnie z niego korzysta. W naszym teście arkusz z rozmiarem zapisanym jako A1:B3 i danymi do wiersza 6 dał 2 wiersze zamiast 5. Opcja InferSheetDimensions każe ustalić obszar na podstawie zawartości arkusza i przywróciła wszystkie wiersze.

Jak to rozwiązać krok po kroku

1. Sprawdź, z czego czyta zapytanie: otwórz je w edytorze, kliknij krok Źródło i spójrz na pasek formuły. Zapis w rodzaju Excel.CurrentWorkbook(){[Name = "Zakres_sprzedazy"]}[Content] wskazuje nazwę. Zaznacz potem w arkuszu dowolną komórkę danych: jeśli na wstążce nie pojawia się karta Projekt tabeli, dane nie są tabelą Excela, a nazwa z formuły to nazwany zakres.
2. Zamień dane na tabelę: zaznacz dowolną komórkę danych, na karcie Narzędzia główne kliknij Formatuj jako tabelę, wybierz styl i sprawdź w oknie, czy zakres obejmuje nagłówek i wszystkie wiersze, także te dopisane ostatnio. Na karcie Projekt tabeli nadaj tabeli czytelną nazwę, na przykład tSprzedaz.
3. Przepnij zapytanie na tabelę: w kroku Źródło zmień nazwę w formule na = Excel.CurrentWorkbook(){[Name = "tSprzedaz"]}[Content]. Jeśli zaraz za krokiem Źródło stoi krok Nagłówki o podwyższonym poziomie, usuń go: tabela ma nagłówki z definicji, a ten krok zamieniłby w nagłówki pierwszy wiersz danych. Możesz też zbudować zapytanie od nowa: kliknij komórkę tabeli i na karcie Dane wybierz Z tabeli/zakresu.
4. Gdy źródłem jest inny skoroszyt, sprawdź rodzaj obiektu w kroku nawigacji. Zapis Źródło{[Item = "Zakres_sprzedazy", Kind = "DefinedName"]}[Data] oznacza nazwany zakres o stałym adresie. Wskaż zamiast niego tabelę (Kind = "Table") albo cały arkusz (Kind = "Sheet"), który obejmuje zajęty obszar. W naszym teście arkusz zwrócił wszystkie 5 wierszy.
5. Gdy plik pochodzi z innego systemu i nawet odczyt całego arkusza gubi końcowe wiersze, zamień w kroku Źródło argumenty funkcji Excel.Workbook na rekord opcji: Excel.Workbook(File.Contents("C:\Dane\eksport.xlsx"), [DelayTypes = true, InferSheetDimensions = true]). W naszym teście ta opcja przywróciła 5 wierszy arkusza, z którego bez niej przychodziły 2.
6. Ustal sposób dopisywania danych z osobami, które je wprowadzają: wpisywanie w wierszu bezpośrednio pod tabelą albo wklejanie w skrajnej lewej komórce pod jej ostatnim wierszem. Wiersze dopisane z odstępem jednego pustego wiersza nie przylegają do tabeli i nie zostaną do niej dołączone.

Jak sprawdzić, że zadziałało

Dopisz pod tabelą testowy wiersz, odśwież zapytanie i sprawdź, czy pojawił się na końcu tabeli wynikowej, a suma kontrolna wzrosła o jego wartość. Potem usuń testowy wiersz i odśwież jeszcze raz, żeby wynik wrócił do stanu sprzed testu. Jeśli źródłem jest plik z innego systemu, porównaj liczbę wierszy wyniku z liczbą wierszy widoczną w tym pliku po otwarciu go w Excelu.

Żeby problem nie wrócił, buduj zapytania na tabelach Excela, a nie na zaznaczonych zakresach czy nazwach o stałym adresie. Nazwa tabeli w kroku Źródło nie zależy od adresu komórek, więc tabela może rosnąć i przesuwać się bez zmian w zapytaniu. Jeśli musisz zostać przy nazwanym zakresie, sprawdzaj po każdym dopisaniu danych, czy jego adres obejmuje nowe wiersze.

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