Spis treści
Niemal na każdym szkoleniu Power Query, kiedy dochodzimy do tego, jak aktualizować utworzone zapytania i ludzie widzą, jak nowe dane zastępują stare dane podczas aktualizacji, jeden z słuchaczy pyta mnie: „czy można się upewnić, że podczas aktualizacji stare dane są też gdzieś zostały zapisane i cała historia aktualizacji była widoczna?
Pomysł nie jest nowy i standardową odpowiedzią będzie „nie” – Power Query jest domyślnie skonfigurowany tak, aby zastępować stare dane nowymi (co jest wymagane w zdecydowanej większości przypadków). Jeśli jednak naprawdę chcesz, możesz obejść to ograniczenie. A metoda, jak zobaczycie później, jest bardzo prosta.
Rozważmy następujący przykład.
Załóżmy, że jako dane wejściowe mamy plik od klienta (nazwijmy go, powiedzmy, Źródło) z listą produktów, które chce kupić w postaci „inteligentnej” dynamicznej tabeli o nazwie Zastosowanie:
W innym pliku (nazwijmy to przez analogię Odbiornik) tworzymy proste zapytanie do importu tabeli z produktami ze Źródła poprzez Dane – Pobierz dane – Z pliku – Z skoroszytu programu Excel (Dane — Pobierz dane — Z pliku — Ze skoroszytu programu Excel) i wgraj wynikową tabelę do arkusza:
Jeśli w przyszłości klient zdecyduje się wprowadzić zmiany w zamówieniu w swojej kartotece Źródło, następnie po zaktualizowaniu naszego żądania (klikając prawym przyciskiem myszy lub przez Dane – Odśwież wszystko) zobaczymy nowe dane w pliku Odbiornik — wszystkie standardowe.
Teraz upewnijmy się, że podczas aktualizacji stare dane nie są zastępowane nowymi, ale nowe są dołączane do starych – i to z dodaną datą i godziną, aby można było zobaczyć, kiedy te konkretne zmiany zostały wprowadzone. zrobiony.
Krok 1. Dodawanie daty i godziny do oryginalnego zapytania
Otwórzmy wniosek Zastosowanieimportowanie naszych danych z Źródłoi dodaj do niej kolumnę z datą i godziną aktualizacji. Aby to zrobić, możesz użyć przycisku Kolumna niestandardowa Zakładka Dodawanie kolumny (Dodaj kolumnę — Kolumna niestandardowa), a następnie wprowadź funkcję DataGodzina.LokalnyTeraz – analog funkcji TDATA (TERAZ) o Microsoft Excel:
Po kliknięciu OK powinieneś otrzymać ładną kolumnę taką jak ta (nie zapomnij ustawić dla niej formatu daty i czasu za pomocą ikony w nagłówku kolumny):
Jeśli chcesz, to dla tablicy wczytanej do arkusza dla tej kolumny możesz ustawić format daty i czasu z sekundami dla większej dokładności (będziesz musiał dodać dwukropek i „ss” do standardowego formatu):
Krok 2: Zapytanie o stare dane
Teraz utwórzmy kolejne zapytanie, które będzie działać jako bufor, który zapisze stare dane przed aktualizacją. Zaznaczanie dowolnej komórki wynikowej tabeli w pliku Odbiornik, wybierz na karcie Dane Command Z tabeli/zakresu (Dane — Z tabeli/zakresu) or Z liśćmi (z arkusza):
Nic nie robimy z tabelą załadowaną w Power Query, wywołujemy zapytanie, na przykład stare dane i naciśnij Strona główna — Zamknij i wczytaj — Zamknij i wczytaj do… — Tylko utwórz połączenie (Dom — Zamknij i wczytaj — Zamknij i wczytaj do… — Utwórz tylko połączenie).
Krok 3. Łączenie starych i nowych danych
Wróćmy teraz do naszego pierwotnego zapytania Zastosowanie i dodaj do niego od dołu stare dane z poprzedniego żądania bufora za pomocą polecenia Strona główna — Dodaj prośby (Strona główna — Dołącz zapytania):
To wszystko!
Pozostaje wrócić do Excela przez Strona główna — Zamknij i pobierz (Strona główna — Zamknij i załaduj) i spróbuj kilka razy zaktualizować całą naszą strukturę za pomocą przycisku Aktualizacja Wszystko Zakładka Dane (Dane — Odśwież wszystko). Przy każdej aktualizacji nowe dane nie zastąpią starych danych, ale przeniosą je poniżej, zachowując całą historię aktualizacji:
Podobną sztuczkę można zastosować podczas importowania z dowolnych zewnętrznych źródeł (stron internetowych, baz danych, plików zewnętrznych itp.), aby zachować stare wartości dla historii, jeśli jest to potrzebne.
- Tabela przestawna w wielu zakresach danych
- Składanie tabel z różnych plików za pomocą dodatku Power Query
- Zbieranie danych ze wszystkich arkuszy księgi w jedną tabelę