Inhoud
Bij bijna elke Power Query-training, wanneer we leren hoe gemaakte query's moeten worden bijgewerkt en mensen zien hoe nieuwe gegevens oude gegevens vervangen bij het bijwerken, vraagt een van de luisteraars me: "is het mogelijk om ervoor te zorgen dat bij het bijwerken de oude gegevens ook ergens zijn opgeslagen en de hele updategeschiedenis zichtbaar was?
Het idee is niet nieuw en het standaardantwoord hierop is "nee" - Power Query is standaard geconfigureerd om oude gegevens te vervangen door nieuwe (wat in de overgrote meerderheid van de gevallen vereist is). Als u echter echt wilt, kunt u deze beperking omzeilen. En de methode is, zoals je later zult zien, heel eenvoudig.
Beschouw het volgende voorbeeld eens.
Laten we aannemen dat we een bestand van de client hebben als invoergegevens (laten we het noemen, laten we zeggen, bron) met een lijst van producten die hij wil kopen in de vorm van een "slimme" dynamische tafel genaamd Aanvraag:
In een ander bestand (laten we het naar analogie noemen) Ontvanger) we maken een eenvoudige query om een tabel met producten uit de Bron te importeren via Gegevens – Gegevens ophalen – Uit bestand – Uit Excel-werkmap (Gegevens — Gegevens ophalen — Uit bestand — Uit Excel-werkmap) en upload de resulterende tabel naar het blad:
Als de klant in de toekomst besluit om de bestelling in zijn dossier te wijzigen bron, en na het bijwerken van ons verzoek (door met de rechtermuisknop te klikken of via Gegevens – Alles vernieuwen) we zullen de nieuwe gegevens in het bestand zien Ontvanger - allemaal standaard.
Laten we er nu voor zorgen dat bij het updaten de oude gegevens niet worden vervangen door nieuwe, maar dat de nieuwe worden toegevoegd aan de oude - en met de toevoeging van een datum-tijd, zodat kan worden gezien wanneer deze specifieke wijzigingen zijn doorgevoerd. gemaakt.
Stap 1. Een datum-tijd toevoegen aan de oorspronkelijke zoekopdracht
Laten we een verzoek openen Aanvraagonze gegevens importeren van bronen voeg er een kolom met de datum-tijd van de update aan toe. Om dit te doen, kunt u de knop Aangepaste kolom tab Een kolom toevoegen (Kolom toevoegen — Aangepaste kolom)en voer vervolgens de functie in DateTime.LocalNow – analoog van de functie De TDATA (NU) вMicrosoft Excel:
Na het klikken op OK je zou moeten eindigen met een mooie kolom zoals deze (vergeet niet om de datum-tijd-indeling ervoor in te stellen met het pictogram in de kolomkop):
Als u wilt, kunt u voor de plaat die naar het blad voor deze kolom is geüpload, het datum-tijdformaat met seconden instellen voor een grotere nauwkeurigheid (u moet een dubbele punt en "ss" toevoegen aan het standaardformaat):
Stap 2: Oude gegevens opvragen
Laten we nu nog een query maken die zal fungeren als een buffer die de oude gegevens opslaat voordat ze worden bijgewerkt. Een cel van de resulterende tabel in het bestand selecteren Ontvanger, selecteer op het tabblad Data commando Van tafel/bereik (Gegevens — Uit tabel/bereik) or Met bladeren (Van blad):
We doen niets met de tabel die in Power Query is geladen, we noemen de query bijvoorbeeld oude gegevens en druk op Home — Sluiten en laden — Sluiten en laden naar… — Alleen verbinding maken (Home — Sluiten&Laden — Sluiten&Laden naar… — Alleen verbinding maken).
Stap 3. Oude en nieuwe gegevens samenvoegen
Nu terug naar onze oorspronkelijke vraag Aanvraag en voeg er van onderaf de oude gegevens van het vorige bufferverzoek aan toe met het commando Home — Verzoeken toevoegen (Home - Zoekopdrachten toevoegen):
Dat is alles!
Het blijft om terug te keren naar Excel via Home — Sluiten en downloaden (Home — Sluiten&Laden) en probeer een paar keer om onze hele structuur bij te werken met de knop Alles bijwerken tab Data (Gegevens — Alles vernieuwen). Bij elke update zullen de nieuwe gegevens niet de oude gegevens vervangen, maar deze naar beneden pushen, waarbij de volledige updategeschiedenis behouden blijft:
Een vergelijkbare truc kan worden gebruikt bij het importeren van externe bronnen (internetsites, databases, externe bestanden, enz.) Om de oude waarden voor de geschiedenis te behouden als je die nodig hebt.
- Draaitabel over meerdere gegevensbereiken
- Tabellen samenstellen uit verschillende bestanden met Power Query
- Gegevens van alle bladen van het boek in één tabel verzamelen