Indhold
Ved næsten hver Power Query-træning, når vi kommer til, hvordan man opdaterer oprettede forespørgsler, og folk ser, hvordan nye data erstatter gamle data ved opdatering, spørger en af lytterne mig: "er det muligt at sikre, at de gamle data ved opdatering er også et sted blev gemt, og hele opdateringshistorikken var synlig?
Ideen er ikke ny, og standardsvaret på den vil være "nej" - Power Query er som standard konfigureret til at erstatte gamle data med nye (hvilket er påkrævet i langt de fleste tilfælde). Men hvis du virkelig vil, kan du komme uden om denne begrænsning. Og metoden, som du vil se senere, er meget enkel.
Overvej følgende eksempel.
Lad os antage, at vi har en fil fra klienten som inputdata (lad os kalde det, lad os sige, Kilde) med en liste over produkter, som han ønsker at købe i form af en "smart" dynamisk tabel med navnet Anvendelse:
I en anden fil (lad os kalde det analogt Modtager) opretter vi en simpel forespørgsel for at importere en tabel med produkter fra Kilden via Data – Hent data – Fra fil – Fra Excel-projektmappe (Data — Hent data — Fra fil — Fra Excel-projektmappe) og upload den resulterende tabel til arket:
Hvis kunden i fremtiden beslutter at foretage ændringer i ordren i sin fil Kilde, derefter efter opdatering af vores anmodning (ved at højreklikke eller via Data – Opdater alle) vil vi se de nye data i filen Modtager - alt standard.
Lad os nu sørge for, at de gamle data ved opdatering ikke erstattes af nye, men de nye tilføjes til de gamle – og med tilføjelse af en dato-tid, så det kan ses, hvornår disse specifikke ændringer blev foretaget. lavet.
Trin 1. Tilføjelse af en dato-tid til den oprindelige forespørgsel
Lad os åbne en anmodning Anvendelseimportere vores data fra Kilde, og tilføj en kolonne med dato-klokkeslæt for opdateringen. For at gøre dette kan du bruge knappen Brugerdefineret kolonne fanen Tilføjelse af en kolonne (Tilføj kolonne – tilpasset kolonne), og indtast derefter funktionen DateTime.LocalNow – analog af funktionen TDATA (NU) i Microsoft Excel:
Efter at have klikket på OK du skulle ende med en smuk kolonne som denne (glem ikke at indstille dato-tidsformatet for den med ikonet i kolonneoverskriften):
Hvis du vil, så for pladen, der er uploadet til arket for denne kolonne, kan du indstille dato-tidsformatet med sekunder for større nøjagtighed (du bliver nødt til at tilføje et kolon og "ss" til standardformatet):
Trin 2: Forespørg efter gamle data
Lad os nu oprette en anden forespørgsel, der vil fungere som en buffer, der gemmer de gamle data før opdatering. Valg af en hvilken som helst celle i den resulterende tabel i filen Modtager, vælg på fanen data Kommando Fra tabel/område (Data — Fra tabel/område) or Med blade (Fra ark):
Vi gør intet med tabellen indlæst i Power Query, vi kalder forespørgslen f.eks. gamle data og tryk Hjem — Luk og indlæs — Luk og indlæs til... — Opret kun forbindelse (Hjem — Luk&indlæs — Luk&indlæs til... — Opret kun forbindelse).
Trin 3. Sammenføjning af gamle og nye data
Nu tilbage til vores oprindelige forespørgsel Anvendelse og føj til det nedefra de gamle data fra den tidligere bufferanmodning med kommandoen Hjem — Tilføj anmodninger (Hjem – Tilføj forespørgsler):
Det er alt!
Det er tilbage at vende tilbage til Excel igennem Hjem — Luk og download (Hjem — Luk og indlæs) og prøv et par gange at opdatere hele vores struktur med knappen Opdatering Alle fanen data (Data – Opdater alle). Med hver opdatering erstatter de nye data ikke de gamle data, men vil skubbe dem nedenunder og bevare hele opdateringshistorikken:
Et lignende trick kan bruges, når du importerer fra eksterne kilder (internetsider, databaser, eksterne filer osv.) for at beholde de gamle værdier for historie, hvis du har brug for det.
- Pivottabel på tværs af flere dataområder
- Samling af tabeller fra forskellige filer ved hjælp af Power Query
- Indsamling af data fra alle ark i bogen i én tabel