Innhold
På nesten hver Power Query-trening, når vi kommer til hvordan man oppdaterer opprettede spørringer og folk ser hvordan nye data erstatter gamle data ved oppdatering, spør en av lytterne meg: «er det mulig å sørge for at når du oppdaterer, er de gamle dataene også et sted ble lagret og hele oppdateringshistorikken var synlig?
Ideen er ikke ny og standardsvaret på den vil være "nei" – Power Query er som standard konfigurert til å erstatte gamle data med nye (noe som kreves i de aller fleste tilfeller). Men hvis du virkelig vil, kan du omgå denne begrensningen. Og metoden, som du vil se senere, er veldig enkel.
Tenk på følgende eksempel.
La oss anta at vi har en fil fra klienten som inputdata (la oss kalle det, la oss si, kilde) med en liste over produkter han ønsker å kjøpe i form av et "smart" dynamisk bord kalt Søknad:
I en annen fil (la oss kalle det analogt Mottaker) lager vi en enkel spørring for å importere en tabell med produkter fra kilden via Data – Hent data – Fra fil – Fra Excel-arbeidsbok (Data — Hent data — Fra fil — Fra Excel-arbeidsbok) og last opp den resulterende tabellen til arket:
Hvis klienten i fremtiden bestemmer seg for å gjøre endringer i bestillingen i sin fil kilde, deretter etter oppdatering av forespørselen vår (ved å høyreklikke eller via Data – Oppdater alle) vil vi se de nye dataene i filen Mottaker - alt standard.
La oss nå sørge for at ved oppdatering erstattes ikke de gamle dataene med nye, men de nye legges til de gamle – og med tillegg av en dato-klokkeslett, slik at det kan ses når disse spesifikke endringene ble laget.
Trinn 1. Legge til en dato-klokkeslett i den opprinnelige spørringen
La oss åpne en forespørsel Søknadimportere våre data fra kilde, og legg til en kolonne med dato-klokkeslett for oppdateringen. For å gjøre dette kan du bruke knappen Egendefinert kolonne tab Legger til en kolonne (Legg til kolonne – egendefinert kolonne), og skriv deretter inn funksjonen DateTime.LocalNow – analog av funksjonen TDATA (NÅ) i Microsoft Excel:
Etter å ha klikket på OK du bør ende opp med en pen kolonne som denne (ikke glem å angi dato- og klokkeslettformatet for den med ikonet i kolonneoverskriften):
Hvis du vil, for platen som er lastet opp til arket for denne kolonnen, kan du angi dato- og klokkeslettformatet med sekunder for større nøyaktighet (du må legge til et kolon og "ss" til standardformatet):
Trinn 2: Spør etter gamle data
La oss nå lage en annen spørring som vil fungere som en buffer som lagrer de gamle dataene før oppdatering. Velge en hvilken som helst celle i den resulterende tabellen i filen Mottaker, velg på fanen Data Kommando Fra Tabell/Rekkevidde (Data – Fra tabell/område) or Med blader (Fra ark):
Vi gjør ingenting med tabellen lastet i Power Query, vi kaller spørringen, for eksempel, gamle data og trykk Hjem — Lukk og last — Lukk og last til... — Opprett kun tilkobling (Hjem — Lukk&last — Lukk&last til... — Opprett kun tilkobling).
Trinn 3. Slå sammen gamle og nye data
Nå tilbake til vår opprinnelige forespørsel Søknad og legg til den fra under de gamle dataene fra forrige bufferforespørsel med kommandoen Hjem — Legg til forespørsler (Hjem – Legg til spørringer):
Det er alt!
Det gjenstår å gå tilbake til Excel gjennom Hjem — Lukk og last ned (Hjem — Lukk og last) og prøv et par ganger å oppdatere hele strukturen vår med knappen Oppdater alle tab Data (Data – Oppdater alle). Med hver oppdatering vil ikke de nye dataene erstatte de gamle dataene, men skyve dem under, og beholde hele oppdateringsloggen:
Et lignende triks kan brukes når du importerer fra eksterne kilder (Internettsider, databaser, eksterne filer, etc.) for å beholde de gamle verdiene for historikk hvis du trenger det.
- Pivottabell over flere dataområder
- Sette sammen tabeller fra forskjellige filer ved hjelp av Power Query
- Samle data fra alle arkene i boken i én tabell