Obsah
Téměř na každém školení Power Query, když se dostaneme k tomu, jak aktualizovat vytvořené dotazy a lidé vidí, jak nová data při aktualizaci nahrazují stará data, se mě jeden z posluchačů ptá: „je možné zajistit, aby při aktualizaci byla stará data také byly někde uloženy a byla vidět celá historie aktualizací?
Myšlenka není nová a standardní odpovědí na ni bude „ne“ – Power Query je ve výchozím nastavení nakonfigurován tak, aby nahradil stará data novými (což je v naprosté většině případů vyžadováno). Pokud však opravdu chcete, můžete toto omezení obejít. A metoda, jak uvidíte později, je velmi jednoduchá.
Zvažte následující příklad.
Předpokládejme, že máme jako vstupní data soubor od klienta (říkejme tomu, řekněme, Zdroj) se seznamem produktů, které chce koupit ve formě „chytré“ dynamické tabulky s názvem editaci videa:
V jiném souboru (říkejme tomu analogicky Přijímač) vytvoříme jednoduchý dotaz pro import tabulky s produkty ze zdroje přes Data – Získat data – Ze souboru – Ze sešitu aplikace Excel (Data — Získat data — Ze souboru — Ze sešitu Excel) a nahrajte výslednou tabulku na list:
Pokud se klient v budoucnu rozhodne provést změny objednávky ve svém souboru Zdroj, poté po aktualizaci našeho požadavku (kliknutím pravým tlačítkem nebo přes Data – Obnovit vše) uvidíme nová data v souboru Přijímač — vše standardní.
Nyní se přesvědčme, že při aktualizaci se stará data nenahrazují novými, ale nová se připojují ke starým – a to s doplněním data-času, aby bylo vidět, kdy k těmto konkrétním změnám došlo vyrobeno.
Krok 1. Přidání data a času k původnímu dotazu
Otevřeme žádost editaci videaimport našich dat z Zdroja přidejte do něj sloupec s datem a časem aktualizace. K tomu můžete použít tlačítko Vlastní sloupec Karta Přidání sloupce (Přidat sloupec – Vlastní sloupec)a poté zadejte funkci DateTime.LocalNow – analog funkce TDATA (NYNÍ) v aplikaci Microsoft Excel:
Po kliknutí na tlačítko OK měli byste skončit s pěkným sloupcem, jako je tento (nezapomeňte pro něj nastavit formát data a času pomocí ikony v záhlaví sloupce):
Pokud chcete, můžete pro desku nahranou na list pro tento sloupec nastavit formát data a času se sekundami pro větší přesnost (budete muset přidat dvojtečku a „ss“ do standardního formátu):
Krok 2: Dotaz na stará data
Nyní vytvoříme další dotaz, který bude fungovat jako vyrovnávací paměť, která uloží stará data před aktualizací. Výběr libovolné buňky výsledné tabulky v souboru Přijímač, vyberte na kartě Data Příkaz Z tabulky/rozsahu (Data – z tabulky/rozsahu) or S listy (Z listu):
S tabulkou načtenou v Power Query neděláme nic, dotaz nazýváme např. stará data a stiskněte Domů — Zavřít a načíst — Zavřít a načíst do… — Pouze vytvořit připojení (Domů — Zavřít&Načíst — Zavřít&Načíst do… — Vytvořit pouze připojení).
Krok 3. Spojení starých a nových dat
Nyní zpět k našemu původnímu dotazu editaci videa a přidejte do něj zespodu stará data z předchozího požadavku na vyrovnávací paměť pomocí příkazu Domů — Přidat požadavky (Domů — Připojit dotazy):
To je vše!
Zbývá se vrátit do Excelu skrz Domů — Zavřete a stáhněte (Domů — Zavřít a načíst) a zkuste několikrát aktualizovat celou naši strukturu pomocí tlačítka Aktualizovat vše Karta Data (Data – Obnovit vše). Při každé aktualizaci nová data nenahradí stará data, ale posunou je níže a zachovají celou historii aktualizací:
Podobný trik lze použít při importu z jakýchkoliv externích zdrojů (internetové stránky, databáze, externí soubory atd.), abyste v případě potřeby zachovali staré hodnoty pro historii.
- Kontingenční tabulka napříč více datovými rozsahy
- Sestavení tabulek z různých souborů pomocí Power Query
- Sběr dat ze všech listů knihy do jedné tabulky